# SQL Server – how to get date differences

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.

### 8 Responses to “SQL Server – how to get date differences”

1. 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

Like

• Sandy,

This will get you everyone with a Start Date after today minus one year:
```select * from UPR00100 where STRTDATE > DATEADD(yy,-1,GETDATE())```

If you want to exclude future start dates, you could add
`and STRTDATE <= GETDATE()`

-Victoria

Like

2. 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.

Like

• Justin,

You could use something like this:
```select * from YourTable where (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

Like

• I am trying to simply do a date diff between two dates and I am not able to account for the leap years, I could not understand your logic. Suppose I want to find difference between ‘9/25/2012’ and today, I should be getting 549 but I am getting 548 rather.

Like

• Supreet,

Following the example above, the difference between 2 dates in days can be coded the following way:

select datediff(d, ‘9/25/2012’, ‘3/28/2014’)

I get 549 when I execute that – do you get something different?

-Victoria

Like

1. - September 13, 2010

Coding specific dates in SQL Server…

Quite often when writing reports I need to write a formula to calculate dates. Dates are not very straightforward…

Like

2. - September 13, 2010

[…] so that I do not have to re-write the code every time. I have previously posted some code on calculating date differences in SQL, however below is some code for getting specific […]

Like