How do I calculate the number of hours between two dates?

Calculating the Number Of Hours Between Two Dates

Starting at least with Version 8.5 of Crystal Reports the DateDiff function provided an easy way to calculate the number of hours between two dates.

If both the starting and ending date and time are stored in DateTime type fields, the calculation is easy:

DateDiff(type,start,end)

Where ôstartö is the starting datetime and ôendö is the ending datetime. ôTypeö is a letter or group of letters indicating the time interval you want the calculation to return as shown below:

Value Description
yyyy Year
q Quarter
m Month
h Hour
n Minuet
s Second

There are additional types as describe in the on-line help.

Here is an example:

DateDiff(ônö,{table.startdatetime},{table.enddatetime})/60


The formula calculates the number of minutes between the two fields and then divides the result by 60 to express the total number of hours as a fraction of an hour.

Some databases do not include datetime fields but rather store dates as date fields and time as character fields. A common practice is to store times as a five-character field: HH:MM using the 24-hour clock. You can still calculate the difference between two events as hours and fraction of an hour by using the following formula:

timeVar starttime := Time(val({table.STIME}[1 to 2]),val({table.STIME}[4 to 5]),0);
dateTimeVar start := Datetime({table.SDATE},starttime);
timevar endtime := Time(val({table.ETIME}[1 to 2]),val({table.ETIME}[4 to 5]),0);
datetimevar end := Datetime({table.EDATE},endtime);
DateDiff ("n",start ,end )/60;

The first and third lines create a time variable from the character fields STIME and ETIME respecrtively.
The second and fourth lines create a datetime variable from the date variables SDATE and EDATE and the time variables starttime and endtime. The last line calculates the number of minutes between the start and end and converts it into hours and fractions of an hour.