DateDiff gives some surprising results

Sunday 22 May 2016 @ 10:00 am

I recently had a user who asked how he could report a bug in Crystal Reports. He was using the DateDiff function and thought it was behaving strangely. He gave me the following example to get the number of hours (h) between two DateTime values:

DateDiff ('h', {@Begin}, {@End} )

He was surprised to see that:
2:10pm to 2:50pm (40 minutes) would return a 0 hour difference while
2:50pm to 3:10pm (20 minutes) would return a 1 hour difference.

I explained that this isn’t a bug, but it is exactly what DateDiff was designed to do. This is true in Crystal Reports and pretty much any other language that uses DateDiff. When you ask for the difference in hours DateDiff truncates both DateTime values to the hour and ignores everything after that. Then it subtracts one truncated value from the other. Here is an example copied directly from the CR “Help” that explains the same thing for doing a DateDiff with years (yyyy):

DateDiff ("yyyy", #12/31/1999#, #1/1/2000#)

Returns 1 (a 1 year difference), even though there is only a 1 day difference between the dates.

DateDiff ("yyyy", #1/1/1999#, #12/31/1999#)

Returns 0 (a 0 year difference), even though there is a 364 day difference.
So, if you want to be more precise than that you need to specify a more precise interval. For instance, when I want to do hours or minutes I will usually do a DateDiff in seconds and either divide by 60 or 3600 to get minutes or hours.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

Leave a Reply

Recrystallize Pro