October 27, 2008
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:
select getdate()
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:
64
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
Result:
37.216438
And divide by 365.25 today:
select cast(datediff(d, '8/19/1971', getdate()) AS DECIMAL (8,1))/365.25
Result:
37.1909650
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.
Victoria, I’m trying to figure out how I could get employees that were hired in the last 12 months. I would use the computer date and the StartDate from Payroll Master table, but can’t seem to put the syntax together correctly.
Thanks,
Sandy
Sandy,
This will get you everyone with a Start Date after today minus one year:
select * from UPR00100where STRTDATE > DATEADD(yy,-1,GETDATE())
If you want to exclude future start dates, you could add
and STRTDATE <= GETDATE()-Victoria
How would I go about selecting records from a table where their last login date was greater than today’s date less 2 years? That way I only get users that have logged in within the last two years…
thanks for your help if you can.
Justin,
You could use something like this:
select * from YourTablewhere (select cast(datediff(d, LastLogin, getdate())
AS DECIMAL (8,1))/365) <= 2
That should give you every row from YourTable with a LastLogin in the last 2 years.
-Victoria