Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Business Objects: Crystal Reports 4 Other topics FAQ

Common Formulas

How do I subtract one datetime field from another and display days, hours, minutes and seconds? by HowardHammerman
Posted: 27 Oct 01

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:


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
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.
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.

Back to Business Objects: Crystal Reports 4 Other topics FAQ Index
Back to Business Objects: Crystal Reports 4 Other topics Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close