If you’re getting some really weird results when using the DATEDIFF() function in SQL Server, and you’re convinced the function contains a bug, don’t tear your hair out just yet. It’s probably not a bug.
There are scenarios where the results produced by this function can be pretty whacky. And if you don’t understand how the function actually works, the results will look completely wrong.
Hopefully this article can help clarify how the DATEDIFF() function is designed to work, and provide some example scenarios of where your results might not be as you’d expect.
Example 1 – 365 Days is Not Always a Year
Question: When is 365 days not a Year?
Answer: When using DATEDIFF() of course!
Here’s an example where I use DATEDIFF() to return the number of days between two dates, and then the number of years between the same two dates.
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 think this result is wrong, and that DATEDIFF() obviously has a bug, read on – not everything is as it seems.
Believe it or not, this is actually the expected result. This result is exactly in accordance with how DATEDIFF() is designed to work.
Example 2 – 100 Nanoseconds = 1 Year?
Let’s take it the other way.
DECLARE @startdate datetime2 = '2016-12-31 23:59:59.9999999', @enddate datetime2 = '2017-01-01 00:00:00.0000000'; SELECT DATEDIFF(year, @startdate, @enddate) Year, DATEDIFF(quarter, @startdate, @enddate) Quarter, DATEDIFF(month, @startdate, @enddate) Month, DATEDIFF(dayofyear, @startdate, @enddate) DOY, DATEDIFF(day, @startdate, @enddate) Day, DATEDIFF(week, @startdate, @enddate) Week, DATEDIFF(hour, @startdate, @enddate) Hour, DATEDIFF(minute, @startdate, @enddate) Minute, DATEDIFF(second, @startdate, @enddate) Second, DATEDIFF(millisecond, @startdate, @enddate) Millisecond, DATEDIFF(microsecond, @startdate, @enddate) Microsecond, DATEDIFF(nanosecond, @startdate, @enddate) Nanosecond;
Results (shown with vertical output):
Year | 1 Quarter | 1 Month | 1 DOY | 1 Day | 1 Week | 1 Hour | 1 Minute | 1 Second | 1 Millisecond | 1 Microsecond | 1 Nanosecond | 100
There’s only a hundred nanoseconds (.0000001 second) difference between the two dates/times, yet we get exactly the same result for every datepart, except nanoseconds.
How can this happen? How can it be 1 microsecond difference and 1 year difference both at the same time? Not to mention all the dateparts in between?
It might seem crazy, but this is not a bug either. These results are exactly in accordance with how DATEDIFF() is supposed to work.
And to make things even more confusing, we could get different results depending on the data type. But we’ll get to that soon. First let’s look at how the DATEDIFF() function actually works.
The Actual Definition of DATEDIFF()
The reason we get the results we do is because the DATEDIFF() function is defined as follows:
This function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate.
Pay particular attention to the words “datepart boundaries crossed”. This is why we get the results we do in the previous examples. It’s easy to assume that DATEDIFF() uses elapsed time for its calculations, but it doesn’t. It uses the number of datepart boundaries crossed.
In the first example, the dates didn’t cross any year-part boundaries. The year of the first date was exactly the same as the year of the second date. No boundaries were crossed.
In the second example, we had the opposite scenario. The dates crossed every datepart boundary at least once (100 times for nanoseconds).
Example 3 – A Different Result for Week
Now, let’s pretend a whole year has gone past. And here we are exactly one year later with the date/time values, except that the year values have incremented by one.
We should get the same results, right?
DECLARE @startdate datetime2 = '2017-12-31 23:59:59.9999999', @enddate datetime2 = '2018-01-01 00:00:00.0000000'; SELECT DATEDIFF(year, @startdate, @enddate) Year, DATEDIFF(quarter, @startdate, @enddate) Quarter, DATEDIFF(month, @startdate, @enddate) Month, DATEDIFF(dayofyear, @startdate, @enddate) DOY, DATEDIFF(day, @startdate, @enddate) Day, DATEDIFF(week, @startdate, @enddate) Week, DATEDIFF(hour, @startdate, @enddate) Hour, DATEDIFF(minute, @startdate, @enddate) Minute, DATEDIFF(second, @startdate, @enddate) Second, DATEDIFF(millisecond, @startdate, @enddate) Millisecond, DATEDIFF(microsecond, @startdate, @enddate) Microsecond, DATEDIFF(nanosecond, @startdate, @enddate) Nanosecond;
Results:
Year | 1 Quarter | 1 Month | 1 DOY | 1 Day | 1 Week | 0 Hour | 1 Minute | 1 Second | 1 Millisecond | 1 Microsecond | 1 Nanosecond | 100
Wrong.
Most of them are the same, but this time the week returned 0.
Huh?
This happened because the input dates have the same calendar week values. It just so happened that the dates chosen for example 2 had different calendar week values.
To be more specific, example 2 crossed week-part boundaries going from ‘2016-12-31’ to ‘2017-01-01’. This is because the last week of 2016 ended on 2016-12-31, and the first week of 2017 started on 2017-01-01 (Sunday).
But in example 3, the first week of 2018 actually began on our start date of 2017-12-31 (Sunday). Our end date, being the next day, fell within the same week. Therefore, no week-part boundaries were crossed.
This obviously assumes that Sunday is the first day of every week. As it turns out, the DATEDIFF() function does assume that Sunday is the first day of the week. It even ignores your SET DATEFIRST setting (this setting allows you to explicitly specify which day is deemed to be the first day of the week). Microsoft’s reasoning for ignoring SET DATEFIRST is that it ensures the DATEDIFF() function is deterministic. Here’s a workaround if this is a problem for you.
So in a nutshell, your results could look “wrong” for any datepart depending on the dates/times. Your results can look extra wrong when using the week-part. And they could look even more wrong if you use a SET DATEFIRST value other than 7 (for Sunday) and you’re expecting DATEDIFF() to honor that.
But the results aren’t wrong, and it’s not a bug. It’s just more of a “gotcha” for those unaware of how the function actually works.
All of these gotchas also apply to the DATEDIFF_BIG() function. It works the same as DATEDIFF() with the exception that it returns the result as a signed bigint (as opposed to an int for DATEDIFF()).
Example 4 – Results Depend on Data Type
You could also get unexpected results due to the data type you use for your input dates. Results will often differ depending on the input dates’ data type. But you can’t blame DATEDIFF() for this, as it’s purely due to the capabilities and limitations of the various data types. You can’t expect to get high precision results from a low precision input value.
For example, whenever the start date or end date has a smalldatetime value, the seconds and milliseconds will always return 0. This is because the smalldatetime data type is only accurate to the minute.
Here’s what happens if we switch example 2 to use smalldatetime instead of datetime2:
DECLARE @startdate smalldatetime = '2016-12-31 23:59:59', @enddate smalldatetime = '2017-01-01 00:00:00'; SELECT DATEDIFF(year, @startdate, @enddate) Year, DATEDIFF(quarter, @startdate, @enddate) Quarter, DATEDIFF(month, @startdate, @enddate) Month, DATEDIFF(dayofyear, @startdate, @enddate) DOY, DATEDIFF(day, @startdate, @enddate) Day, DATEDIFF(week, @startdate, @enddate) Week, DATEDIFF(hour, @startdate, @enddate) Hour, DATEDIFF(minute, @startdate, @enddate) Minute, DATEDIFF(second, @startdate, @enddate) Second, DATEDIFF(millisecond, @startdate, @enddate) Millisecond, DATEDIFF(microsecond, @startdate, @enddate) Microsecond, DATEDIFF(nanosecond, @startdate, @enddate) Nanosecond;
Result:
Year | 0 Quarter | 0 Month | 0 DOY | 0 Day | 0 Week | 0 Hour | 0 Minute | 0 Second | 0 Millisecond | 0 Microsecond | 0 Nanosecond | 0
The reason these are all zero is because both input dates are actually identical:
DECLARE @startdate smalldatetime = '2016-12-31 23:59:59', @enddate smalldatetime = '2017-01-01 00:00:00'; SELECT @startdate 'Start Date', @enddate 'End Date';
Result:
+---------------------+---------------------+ | Start Date | End Date | |---------------------+---------------------| | 2017-01-01 00:00:00 | 2017-01-01 00:00:00 | +---------------------+---------------------+
The limitations of the smalldatetime data type caused the seconds to be rounded up, which then caused a flow on effect and everything got rounded up. Even if you don’t end up with identical input values, you could still get an unexpected result due to the data type not providing the precision you need.