5.7 Calculating Durations

5.7.1 Problem

You want to find out how many seconds, minutes, hours, days, or months have passed between two dates. Additionally, you want to calculate interest at a 5% yearly interest rate for 100 USD between those same two dates.

5.7.2 Solution

The first part of this problem is a typical date-arithmetic assignment, and the results can be calculated using the DATEDIFF function. In the following example, 2001-1-1 is used for the beginning date, and CURRENT_TIMESTAMP supplies the current date as the ending date.

SELECT 
   DATEDIFF(second, '2001-1-1',CURRENT_TIMESTAMP) seconds,
   DATEDIFF(minute, '2001-1-1',CURRENT_TIMESTAMP) minutes,
   DATEDIFF(hour, '2001-1-1',CURRENT_TIMESTAMP) hours,
   DATEDIFF(day, '2001-1-1',CURRENT_TIMESTAMP) days,
   DATEDIFF(month, '2001-1-1',CURRENT_TIMESTAMP) months

seconds     minutes     hours       days        months      
----------- ----------- ----------- ----------- ----------- 
26753371    445889      7431        309         10

DATEDIFF Returns Whole Units Be careful when interpreting the results of a call to DATEDIFF. That function only returns whole units; any fractional values are truncated. For example, you can execute DATEDIFF(month, '2001-1-1',CURRENT_TIMESTAMP) anytime during the month of November and get the same value: 10. That's because until you reach December 1, 2001, a full 11 months have not passed. Fractional values are also not reported for seconds, minutes, hours, and days, but the lack thereof is most noticeable with large units, such as months.

The interest calculation is a bit more noteworthy:

SELECT 
   100*(POWER(1.0500, CONVERT(NUMERIC(10,4),DATEDIFF(d, '2001-1-1', 
      CURRENT_TIMESTAMP)/365.0000)))-100 Interest

Interest                                 
---------------------------------------- 
4.2900

The result is in money units per 100 units, which, in this case, works out to $4.29 interest on our original amount of $100.00. Obviously, you'll get a different result depending on which day you run the query.

5.7.3 Discussion

The DATEDIFF function is a perfect tool for computing durations between two instants (dates). As a rule of thumb, you should always consider using it when dealing with calculations involving periods of time.

In the second query, the DATEDIFF function is used to calculate the number of days from January 1, 2001. With that information, the interest is calculated using the following formula:

Amount*1.0500^(NumDays/365.0000)

Please note, we wrote 365.0000 in the query, and not 365, on purpose. Using the latter value will result in integer arithmetic: decimal values will be truncated, reducing the interest rate to 0.

Similarly, we had to CAST the DATEDIFF result to the numeric type NUMERIC(10,4). The POWER function requires both arguments to have the same precision, so we expressed the interest rate as 1.0500. Because we are calculating interest, we need such precision to calculate meaningful results.