5.7 Calculating Durations5.7.1 ProblemYou 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 SolutionThe 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
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 DiscussionThe 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. |