Often, when writing a report, we need to calculate the difference between two dates or the difference between a date and today. For example – how old is an invoice? How many days overdue is an invoice? Below is some SQL code that will help.
First, if you need to dynamically get the current date (and time) in SQL:
To calculate the difference between two dates and get the result in days:
select datediff (d, fromDATE, toDATE)
For example, this code:
select datediff (d, '8/24/08', '10/27/08')
Will produce the following result:
Note that you put the earlier date first to get a positive result. There may also be times when you want to get the difference not in days, but in months, years or minutes. Here is the syntax:
Months: select datediff (mm, fromDATE, toDATE) Years: select datediff (yy, fromDATE, toDATE) Minutes: select datediff (mi, fromDATE, toDATE)
All of the above will give you integer results, rounding down. That’s typically good enough for differences in days, however for years typically it is more useful to see the results with decimal places. In this case, I get the results in days and divide by 365 (or 365.25 if I think I am using a long enough time span where I need to allow for leap years):
select cast(datediff(d, fromDATE, toDATE) AS DECIMAL (12,1))/365
Here is an example that also demonstrates the leap year difference. Divide by 365 today:
select cast(datediff(d, '8/19/1971', getdate()) AS DECIMAL (8,1))/365
And divide by 365.25 today:
select cast(datediff(d, '8/19/1971', getdate()) AS DECIMAL (8,1))/365.25
Not a huge difference, but a little more precise if you’re going to be doing calculations with that many years.
If you are looking for more information and examples on working with dates in SQL Server, take a look at this website.