INTELLIGENT WORK FORUMS FOR COMPUTER PROFESSIONALS
Log In
Come Join Us!
Are you a Computer / IT professional? Join TekTips Forums!
 Talk With Other Members
 Be Notified Of Responses
To Your Posts
 Keyword Search
 OneClick Access To Your
Favorite Forums
 Automated Signatures
On Your Posts
 Best Of All, It's Free!
*TekTips's functionality depends on members receiving email. By joining you are opting in to receive email.
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
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 onesecond 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 8007832269 Hammerman Associates, Inc. provide Crystal Reports training, consulting, course material, utilities and software. Consultants are available throughout North America for short or longterm assignments.

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


