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.

        Thanks in advance !

        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

Trackbacks/Pingbacks

  1. Victoria Yudin - 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. Coding specific dates in SQL Server « Victoria Yudin - 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: