Print Page | Close Window

DateTime conversion

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=8419
Printed Date: 02 May 2024 at 8:05pm


Topic: DateTime conversion
Posted By: rp123
Subject: DateTime conversion
Date 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


Replies:
Posted By: kevlray
Date 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.

Posted By: rp123
Date Posted: 20 Nov 2009 at 2:52pm
hi,

can you please write don the formula for me. I am a new learner.


-------------
rp

Posted By: kevlray
Date 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) 

Posted By: kevlray
Date 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.

Posted By: DBlank
Date 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.

Posted By: Vertex
Date 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

Posted By: DBlank
Date 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


Print Page | Close Window