Subtracting Two DateTime Fields

[GMAN33]

Hello,

I would like to subtract two datetime fields to get how long a process takes and would like to have the results come out like "0 hours, 2 minutes, 37 seconds" They are within the same table.

Any help on the formula would be appreciated

Thanks

 

[pgtek]

Hi
check this out
------------------------
The information in the article refers to:
Seagate Crystal Reports 8


Applies to:

All versions
Adding days, months years and so on to a date field
formulas
Crystal Reports version 6 and 7


Synopsis

How do you add or subtract a specified date interval, for example days, months, years and so on, from a date field?

Solution

To add or subtract days, months, or years from a date field use the DateAdd function. This function is installed automatically in Crystal Reports version 8 and higher, for both Crystal and Basic syntax.

Note:=======

If you have version 6 or 7 of Crystal Reports, to obtain the DateAdd function go to

http://support.crystaldecisions.com.

Click 'Files/Updates', then click 'Find a file'.
Search for UFLDateAdd.exe.
Download and run this file, then follow the installation instructions in readme.txt

============

The following information applies to using Crystal Syntax or Basic Syntax in the Formula editor.

DateAdd (intervalType, nIntervals, startDateTime)

The DateAdd function takes three parameters:

- intervalType is a String expression specifying the interval of time to be added or subtracted.

Note:========

For a complete list of the interval types, for example days, weeks, and so on, refer to DateAdd in the Crystal Reports online help.

============

- nIntervals is a Number or numeric expression specifying the number of intervals to be added. It can be positive (to get date-times in the future) or negative (to get date-times in the past).

- startDateTime is the DateTime value to which the intervals are to be added or subtracted.

Examples:

//Adds one day to the Order Date field of the Orders table.
DateAdd("D",1, {Orders.OrderDate})

//Subtracts one day from the Order Date field of the Orders table.
DateAdd("D",-1, {Orders.OrderDate})

//Adds one month to the Order Date field of the Orders table.
DateAdd("M",1, {Orders.OrderDate})

//Subtracts one month from the Order Date field of the Orders table.
DateAdd("M",-1, {Orders.OrderDate})

//Adds one year to the Order Date field of the Orders table.
DateAdd("YYYY",1, {Orders.OrderDate})

//Subtracts one year from the Order Date field of the Orders table.
DateAdd("YYYY",-1, {Orders.OrderDate})


------------------------------------------------------------

cheers

pgtek

 

[Naith]

Gman,

The DateTimeDiff function does this.

Download the DateTimeDiff UFL (UFLDTDIF.DLL (16 bit) or U2LDTDIF.DLL (32 bit)) from

http://support.crystaldecisions.com.

Naith

 

[GMAN33]

Thank you for the info but I do not want to add or subtract to or from a date. I just want to subtract two datetime fields and the get the results in a format of "0 hours, 2 minutes, 37 seconds" something like that.

Thanks

 

[GMAN33]

Thanks Naith, I will check this out. Always a help from you!!!

Take care
GMAN

 

[GMAN33]

Naith,

That's a beautiful thing!!!! Thanks for all of the help

GMAN

 

[GMAN33]

Naith...one step further.

Is there a way to get the average time it takes for a process to run in the group footer after I subtract the two dates as mentioned above?

Thanks again
GMAN

 

[Naith]

Can you show me some data, so I get a handle on what you mean?

 

[Naith]

Oh, never mind. I'm not really awake yet, but I think I see what you're getting at.

WhilePrintingRecords;

NumberVar dur;
NumberVar hrs;
NumberVar min;
NumberVar sec;

dur := (DTDiffToSeconds({Date1},{Date2})/Count({IDField},{YourGroupField}));

hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);

ToText(hrs,"0&quot + " hours, " + ToText(min,"00&quot + " minutes, " + ToText(sec,"00&quot + " seconds."

Stick that in your group footer, and you're good to go.

Naith

 

[GMAN33]

Hi Naith,

This is what I have: Grouped by User, showing the process(s) that they run, Start/Stop Time, the time calculation that you helped me get and all I need is the overall average for this group in hours, minutes, seconds.


User 1 Group
_________________________________________________________
Process 1 (S)5:30:33 am (E)5:21:21 am 0 Day(s), 0 Hour(s), 9 Minute(s), 12 Second(s)
Process 2 (S)6:09:55 am (E)6:09:36 am 0 Day(s), 0 Hour(s), 0 Minute(s), 19 Second(s)
Process 3 (S)6:14:00 am (E)6:13:51 am 0 Day(s), 0 Hour(s), 0 Minute(s), 9 Second(s)

User 1 Footer: Average Time for all Processes: ????

Hope this helps
GMAN

 

[GMAN33]

Thanks Naith as always. I will give it a try.

Take care
GMAN

 

[GMAN33]

Naith,

Can you tell me what the IDFIELD is supposed to represent?

dur := (DTDiffToSeconds({Date1},{Date2})/Count({IDField},{YourGroupField}));

Sorry to be a pest.

Thanks
GMAN

 

[Naith]

No problem.

When I say {IDField}, I just mean a core field of your report; a field which has to appear in ever detail line.

I guess in your instance, it would be something like {ProcessID} or {ProcessNumber}.

Naith

 

[GMAN33]

Thanks Naith,

I will give it a try.

GMAN

 

[GMAN33]

Naith,

Can you check this formula. I am getting some output for some groups but not all. I have one group on the report and it is by a creation date, sorted "daily". here is the formula:

WhilePrintingRecords;

NumberVar dur;
NumberVar hrs;
NumberVar min;
NumberVar sec;

dur := (DTDiffToSeconds({BATCH_DM_RUNS.BATCH_START_TS},{BATCH_DM_RUNS.BATCH_END_TS})/COUNT({BATCH_DM_RUNS.CLINICAL_STUDY_ID},{@Date Group}));

hrs := Truncate(truncate(dur/60)/60);
min := Remainder(truncate(dur/60),60);
sec := remainder(dur,60);

totext(hrs, "00&quot + "hours, " + totext(min,"00&quot + "minutes, " + totext(sec,"00&quot + "seconds"

Thanks for the continued help
GMAN

 

[Naith]

The formula looks okay.

Show me an example of a group which gets the average result displayed, as well as one that doesn't.

By no output, do you mean a blank output, or a zero hh:mm:ss value?

Naith

 

[GMAN33]

Naith,

No output, I mean 0 hrs:mins:secs

Here is some output...look at 2nd group

8/8/2003
4:42:37 am 4:41:40 am 0 Day(s), 0 Hour(s), 0 Minute(s), 57 Second(s)
6:28:40 am 6:28:12 am 0 Day(s), 0 Hour(s), 0 Minute(s), 28 Second(s)
10:04:36 am 10:04:13 am 0 Day(s), 0 Hour(s), 0 Minute(s), 23 Second(s)
00hours, 00minutes, 07seconds

8/7/2003
6:02:53 am 6:01:30 am 0 Day(s), 0 Hour(s), 1 Minute(s), 23 Second(s)
8:00:01 am 7:59:08 am 0 Day(s), 0 Hour(s), 0 Minute(s), 53 Second(s)
8:16:50 am 8:16:50 am 0 Day(s), 0 Hour(s), 0 Minute(s), 0 Second(s)
----->00hours, 00minutes, 00seconds

8/6/2003
8:05:05 am 7:03:11 am 0 Day(s), 1 Hour(s), 1 Minute(s), 54 Second(s)
10:43:23 am 10:42:19 am 0 Day(s), 0 Hour(s), 1 Minute(s), 4 Second(s)
11:50:38 am 11:50:31 am 0 Day(s), 0 Hour(s), 0 Minute(s), 7 Second(s)
5:58:20 pm 5:54:24 pm 0 Day(s), 0 Hour(s), 3 Minute(s), 56 Second(s)
00hours, 00minutes, 59seconds

Thanks
GMAN

 

[Naith]

The averages which are being returned are way off. Are you suppressing some rows in your report?

 

[GMAN33]

No supressing or no selection expert. Do you think it has something to do with the way I am grouping them?

 

[Naith]

Okay, this is all down to my bad. (I did tell you I hadn't woken up properly, didn't I? Monday + day before payday <> good productivity for me).

What's going on here is that the formula I gave you is not accumulating the DateTimeDiff, so you're only getting the average of the last row per group.

To overcome this, you want to copy this into your details section:

WhilePrintingRecords;
NumberVar Seconds := Seconds + DTDiffToSeconds({BATCH_DM_RUNS.BATCH_START_TS},{BATCH_DM_RUNS.BATCH_END_TS});

...this into your group header section:

WhilePrintingRecords;
NumberVar Seconds := 0;

...and finally this where your average should be:

WhilePrintingRecords;

NumberVar dur;
NumberVar hrs;
NumberVar min;
NumberVar sec;
NumberVar Seconds;

dur := (Seconds/Count({BATCH_DM_RUNS.CLINICAL_STUDY_ID},{@Date Group}));

hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);

ToText(hrs,&quot;0&quot + &quot; hours, &quot; + ToText(min,&quot;00&quot + &quot; minutes, &quot; + ToText(sec,&quot;00&quot + &quot; seconds.&quot; ;

That should do you, I hope.

Naith