In SQL Server, you can use the T-SQL DATEDIFF() function to return the difference between two dates/times. It works on any expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value.
This article provides examples of the DATEDIFF() function in SQL Server.
Syntax
First, here’s the syntax:
DATEDIFF ( datepart , startdate , enddate )
Where datepart is the part of the date that you want compared. startdate is the first date and enddate is the end date.
The way it works is that it returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate.
Example 1
Here’s a basic example where we find out the number of days between two dates:
SELECT DATEDIFF(day, '2001-01-01', '2002-01-01') AS Result;
Result:
+----------+ | Result | |----------| | 365 | +----------+
Example 2
Here’s another example where I declare two variables and assign two different dates to them (I use DATEADD() to add 1 year to the first date). I then use DATEDIFF() to return various dateparts for that date:
DECLARE @date1 datetime2 = '2000-01-01 00:00:00.0000000';
DECLARE @date2 datetime2 = DATEADD(year, 1, @date1);
SELECT
DATEDIFF( year, @date1, @date2 ) AS Years,
DATEDIFF( quarter, @date1, @date2 ) AS Quarters,
DATEDIFF( month, @date1, @date2 ) AS Months,
DATEDIFF( week, @date1, @date2 ) AS Weeks,
DATEDIFF( dayofyear, @date1, @date2 ) AS DayOfYear,
DATEDIFF( day, @date1, @date2 ) AS Days;
Result:
+---------+------------+----------+---------+-------------+--------+ | Years | Quarters | Months | Weeks | DayOfYear | Days | |---------+------------+----------+---------+-------------+--------| | 1 | 4 | 12 | 53 | 366 | 366 | +---------+------------+----------+---------+-------------+--------+
Example 3
As mentioned, you can also return the time parts between the dates. Here’s an example of returning the number of hours, minutes, and seconds between the date/time values:
DECLARE @date1 datetime2 = '2000-01-01 00:00:00.0000000';
DECLARE @date2 datetime2 = DATEADD(hour, 1, @date1);
SELECT
DATEDIFF( hour, @date1, @date2 ) AS Hours,
DATEDIFF( minute, @date1, @date2 ) AS Minutes,
DATEDIFF( second, @date1, @date2 ) AS Seconds;
Result:
+---------+-----------+-----------+ | Hours | Minutes | Seconds | |---------+-----------+-----------| | 1 | 60 | 3600 | +---------+-----------+-----------+
Example 4
And here’s an example of getting the number of milliseconds, microseconds, and nanoseconds between two date/time values:
DECLARE @date1 datetime2 = '2000-01-01 00:00:00.0000000';
DECLARE @date2 datetime2 = DATEADD(millisecond, 1, @date1);
SELECT
DATEDIFF( millisecond, @date1, @date2 ) AS Milliseconds,
DATEDIFF( microsecond, @date1, @date2 ) AS Microseconds,
DATEDIFF( nanosecond, @date1, @date2 ) AS Nanoseconds;
Result:
+----------------+----------------+---------------+ | Milliseconds | Microseconds | Nanoseconds | |----------------+----------------+---------------| | 1 | 1000 | 1000000 | +----------------+----------------+---------------+
Example 5 – Error!
If you try to do something extreme, like, return the number of nanoseconds in 100 years, you’ll get an error. This is because DATEDIFF() returns an int value, and there are more nanoseconds in 100 years than the int data type can handle.
So here’s what happens if you try to do that:
DECLARE @date1 datetime2 = '2000-01-01 00:00:00.0000000';
DECLARE @date2 datetime2 = DATEADD(year, 100, @date1);
SELECT
DATEDIFF( millisecond, @date1, @date2 ) AS Milliseconds,
DATEDIFF( microsecond, @date1, @date2 ) AS Microseconds,
DATEDIFF( nanosecond, @date1, @date2 ) AS Nanoseconds;
Result:
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
Fortunately, if you really must find out how many nanoseconds are in 100 years, you can use the DATEDIFF_BIG() function instead. This function returns a signed bigint data type, which allows you to return much larger values than DATEDIFF() can.
Example 6 – Getting Weird Results?
The results you get from DATEDIFF() can sometimes look completely wrong if you don’t know how the function actually works.
For example:
DECLARE @startdate datetime2 = '2016-01-01 00:00:00.0000000', @enddate datetime2 = '2016-12-31 23:59:59.9999999'; SELECT DATEDIFF(day, @startdate, @enddate) Days, DATEDIFF(year, @startdate, @enddate) Years;
Result:
+--------+---------+ | Days | Years | |--------+---------| | 365 | 0 | +--------+---------+
If you don’t know how DATEDIFF() actually works, this result could look so wrong, that you’d be forgiven for assuming that it’s a bug. But it’s not a bug.
Check out DATEDIFF() Returns Wrong Results in SQL Server? Read This. to see this example and other cases where the results can look completely wrong, but be perfectly correct (and for an explanation on why they look the way they do).
One of the examples on that page is probably worth mentioning again here. DATEDIFF() actually ignores your SET DATEFIRST value. This can result in unexpected results, especially if you’re in a culture that doesn’t use Sunday as the first day of the week. Check out this Workaround for DATEDIFF() Ignoring SET DATEFIRST in SQL Server if you think this might affect you.