Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: DateTime conversion Post Reply Post New Topic
Author Message
rp123
Groupie
Groupie


Joined: 26 Feb 2009
Location: United States
Online Status: Offline
Posts: 47
Quote rp123 Replybullet Topic: DateTime conversion
    Posted: 20 Nov 2009 at 3:02am
Hi,

Can you please help me convert the datetime field to day, minute, hour, minute and seconds.

I want to get the difference of these fileds (these are datetime fields)

{prob.close.time} - {prob.open.time}

I want to display the difference in month, day, hour, minutes, seconds

i.e.  11/18/09 03:54:00 - 11/19/09 05:00:00 = 1 01:06:00


sample data:

7/25/2009  7:19:58 PM
8/4/2009  2:40:44 PM
8/12/2009  9:25:16 AM
7/8/2009  3:16:19 PM

rp
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 20 Nov 2009 at 9:53am
The only way I could thing you could do is to make a formula to first do a date difference of the date part, then do a date difference on the time part and concate them together (convert to text first).
 
Hope this helps.
IP IP Logged
rp123
Groupie
Groupie


Joined: 26 Feb 2009
Location: United States
Online Status: Offline
Posts: 47
Quote rp123 Replybullet Posted: 20 Nov 2009 at 2:52pm
hi,

can you please write don the formula for me. I am a new learner.
rp
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 24 Nov 2009 at 12:50pm
Well this seems to work pretty well, of course it there is an issue with getting negative numbers in the time portion.  I would have to do further research to get the time value converted to a number as a whole then do the subtraction.

numbervar hourdiff := hour({datetime2})-hour({datetime1});
numbervar daydiff := date({datetime2}) - date({datetime1});
numbervar minutediff := minute({datetime2}) - minute({datetime1});
numbervar seconddiff := second({datetime2}) - second({datetime1});


totext(daydiff)+" "+totext(hourdiff)+":"+totext(minutediff)+":"+totext(seconddiff) 
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 25 Nov 2009 at 9:26am
Thought about it and you would need to take the minute values and multiply by 60 (before subtracting), take the hour values and multiply by 3600, add the second's value + minute's value + hour's value (do all the above for the both the datetime1 and datetime2). Then subtract the two values.  Then you would need to do the mod of both the values to break it back into hours, minutes and seconds.
 
Code is too lenghtly to include here.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 25 Nov 2009 at 9:40am

Can you define this more clearly? You state you want this in Month Day Hour Minutes, but how are you defining a month?
Your example "i.e.  11/18/09 03:54:00 - 11/19/09 05:00:00 = 1 01:06:00" is easy to understand but what about something like...

"11/18/09 03:54:00 - 11/17/09 02:53:00" ?
The month component is not as easy to define here as month lengths are not consistent.
Please define this exactly and it would be easier to assist.


Edited by DBlank - 25 Nov 2009 at 9:53am
IP IP Logged
Vertex
Newbie
Newbie
Avatar

Joined: 29 Oct 2009
Location: India
Online Status: Offline
Posts: 24
Quote Vertex Replybullet Posted: 27 Nov 2009 at 8:54am
hello DBlank

how we can  count these records for Given Timing

count ({Order.OrderID}) ...for today
count ({Order.OrderID}) ...for this month
count ({Order.OrderID}) ...for this year
Hi Peoples
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 23 Dec 2009 at 11:53am
Create 3 running totals to do a count on ({Order.OrderID}).
 
Each running total wll use a formula in the evaluate section
RT for today: table.datetime field = currentdate
RT for this month: table.datetime field in monthtodate
RT for this year: table.datetime field in yeartodate
 
Place each of these 3 RTs on the report footer
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.