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()). It’s just more of a “gotcha” for those unaware of how the function actually works.Īll of these gotchas also apply to the DATEDIFF_BIG() function. 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.īut the results aren’t wrong, and it’s not a bug. Your results can look extra wrong when using the week-part. So in a nutshell, your results could look “wrong” for any datepart depending on the dates/times. Here’s a workaround if this is a problem for you. Microsoft’s reasoning for ignoring SET DATEFIRST is that it ensures the DATEDIFF() function is deterministic. 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). As it turns out, the DATEDIFF() function does assume that Sunday is the first day of the week. This obviously assumes that Sunday is the first day of every week. Therefore, no week-part boundaries were crossed. Our end date, being the next day, fell within the same week. This is because the last week of 2016 ended on, and the first week of 2017 started on (Sunday).īut in example 3, the first week of 2018 actually began on our start date of (Sunday). To be more specific, example 2 crossed week-part boundaries going from ‘’ to ‘’. It just so happened that the dates chosen for example 2 had different calendar week values. This happened because the input dates have the same calendar week values. Most of them are the same, but this time the week returned 0. And here we are exactly one year later with the date/time values, except that the year values have incremented by one. Now, let’s pretend a whole year has gone past. The dates crossed every datepart boundary at least once (100 times for nanoseconds). In the second example, we had the opposite scenario. The year of the first date was exactly the same as the year of the second date. In the first example, the dates didn’t cross any year-part boundaries. It uses the number of datepart boundaries crossed. It’s easy to assume that DATEDIFF() uses elapsed time for its calculations, but it doesn’t. This is why we get the results we do in the previous examples. Pay particular attention to the words “datepart boundaries crossed”. This function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate. The reason we get the results we do is because the DATEDIFF() function is defined as follows: First let’s look at how the DATEDIFF() function actually works. These results are exactly in accordance with how DATEDIFF() is supposed to work.Īnd to make things even more confusing, we could get different results depending on the data type. It might seem crazy, but this is not a bug either. 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? 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. This result is exactly in accordance with how DATEDIFF() is designed to work. If you think this result is wrong, and that DATEDIFF() obviously has a bug, read on – not everything is as it seems.īelieve it or not, this is actually the expected result. 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 datetime2 = ' datetime2 = ' 23:59:59.9999999' 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.Įxample 1 – 365 Days is Not Always a Year And if you don’t understand how the function actually works, the results will look completely wrong. There are scenarios where the results produced by this function can be pretty whacky. 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.
0 Comments
Leave a Reply. |