How do I subtract one datetime field from another and display days, hours, minutes and seconds?

When you subtract one datetime field from another datetime field in Crystal the result is the number of days and the decimal fraction of day. Assume the following dates:

D1 := Datetime(2001,10,24,09,00,00) // 9:00 AM, October 24, 2001
D2 := Datetime(2001,10,24,08,59,59) // 8:59 AM, October 24, 2001
D3 := Datetime(2001,01,01,09,00,00) // 9:00 AM, January 1, 2001

D1 - D2 = .0000115741

There is a one-second difference between D1 and D2. There are 86,400 seconds in a day. Dividing 1 by 86,400 yields .0000115741

D1 - D3 = 296.00000

There are exactly 296 days between D1 and D3. Since there are no hours, minutes or seconds between the two datetimes, the decimal portion of the difference is zero.

D2 - D3 = 295.9999884259

There are 295 days, 23 hours, 59 minutes and 59 seconds between D2 and D3.

86400 seconds times .9999884259 is 86399 or one second less than a day.

Let us suppose that our client required a report that showed the difference between D2 and D3 in days, hours, minutes and seconds formatted in a sentence. For example: "There are 295 days, 23 hours, 59 minutes and 59 seconds between the two dates."

One would think that this is easy with the new DateDiff function. The DateDiff function requires the following syntax:

DateDiff(intervaltype,StartDateTime,EndDateTime)

Where intervaltype is a string with a value such as:

"d" for days
"h" for hours
"n" for minutes
"s" for seconds

However, the expression
DateDiff("d",{@D2},{@D3})
using the examples above, yields 296 days, not the correct 295.

As a result, we must use the following expression:

numbervar tsecs := datediff("s",{@d3},{@d2}); // number of seconds between the dates

numbervar ndays := truncate(tsecs/86400); // divide by the seconds in a day

tsecs := remainder(tsecs,86400); // find the left over seconds

numbervar nhours := truncate(tsecs/3600); // divide by the seconds in an hour

tsecs := remainder(tsecs,3600); // find the left over seconds

numbervar nmin := truncate(tsecs/60); // divide by the seconds in a minute

tsecs := remainder(tsecs,60); // find the left over seconds

// now that we have all the components, we put it together in a sentence

"There are "+totext(ndays,0)+" days, "+totext(nhours,0)+" hours, "+ totext(nmin,0)+
" minutes, "+totext(tsecs,0)+" seconds, between the two dates."

The remainder function divides the second argument into the first and returns the remainder after the division. The Totext function changes numeric values into string values and must be used when appending numbers to text in this way. The zero argument indicates that we want no decimals.

Howard Hammerman, Ph.D.
Hammerman Associates, Inc.
http://www.hammerman.com
800-783-2269
Hammerman Associates, Inc. provide Crystal Reports training,
consulting, course material, utilities and software. Consultants are available throughout North America for short or long-term assignments.