INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• 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.

# 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 faq149-1215 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, 2001D2 := Datetime(2001,10,24,08,59,59) // 8:59 AM, October 24, 2001D3 := Datetime(2001,01,01,09,00,00) // 9:00 AM, January 1, 2001D1 - D2 = .0000115741There is a one-second difference between D1 and D2. There are 86,400 seconds in a day. Dividing 1 by 86,400 yields .0000115741D1 - D3 = 296.00000There 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.9999884259There 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 secondsHowever, 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 datesnumbervar ndays := truncate(tsecs/86400); // divide by the seconds in a daytsecs := remainder(tsecs,86400); // find the left over secondsnumbervar nhours := truncate(tsecs/3600); // divide by the seconds in an hourtsecs := remainder(tsecs,3600); // find the left over secondsnumbervar nmin := truncate(tsecs/60); // divide by the seconds in a minutetsecs := 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.com800-783-2269Hammerman 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

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!