SQL DateDiff
With the DateDiff Function you can determine the interval between two given dates.
Seems pretty simple, but there is more to it than you might think.
How can such a simple Function be the cause of errors in a Query?
Turns out that the basic understanding of how this Function works is the root cause of the problem.
So in this post we will solve the mysterious case of the DateDiff.
How it works
The DateDiff Function is pretty straight forward.
You tell you want a DateDiff and specify what interval you want.
This could be Seconds, Hours, Days or Weeks and so on.
Then you supply the Function with 2 Dates: a Start Date and an End Date.
Looks pretty simple, huh?
Here is an example:
declare @End datetime2 = dateadd(second, 7, @Start)
select @Start, @End,
datediff(second, @Start, @End) as SecondDiff,
datediff(hour, @Start, @End) as HourDiff,
datediff(day, @Start, @End) as DayDiff,
datediff(week, @Start, @End) as WeekDiff,
datediff(month, @Start, @End) as MonthDiff
I took a Start Date, added 7 seconds to it for the End Date.
And as expected, DateDiff tells me the interval is 7 Seconds.
7 Seconds is not nearly an hour, so the Hour interval is 0.
And since 7 Seconds is not nearly an hour, it certainly is 0 days, 0 weeks and 0 months.
Let's try it with a different Start Date:
declare @End datetime2 = dateadd(second, 7, @Start)
select @Start, @End,
datediff(second, @Start, @End) as SecondDiff,
datediff(hour, @Start, @End) as HourDiff,
datediff(day, @Start, @End) as DayDiff,
datediff(week, @Start, @End) as WeekDiff,
datediff(month, @Start, @End) as MonthDiff
Wait a minute?
Now it says it is an Hour, a Day and even a Week off!
That is strange.
But it gets even more weird when we try this one:
declare @End datetime2 = dateadd(second, 7, @Start)
select @Start, @End,
datediff(second, @Start, @End) as SecondDiff,
datediff(hour, @Start, @End) as HourDiff,
datediff(day, @Start, @End) as DayDiff,
datediff(week, @Start, @End) as WeekDiff,
datediff(month, @Start, @End) as MonthDiff
Now it says that these Dates are a Month apart, but not a Week? What is going on here?
How it realy works
DateDiff does not look for how far the Dates are apart.
Instead it looks at the interval you specify in the Function.
It gets those parts from the given Dates and calculates the difference.
It works like this:
--declare @Start datetime2 = '2023-08-05 23:59:55'
declare @Start datetime2 = '2023-07-31 23:59:56'
declare @End datetime2 = dateadd(second, 7, @Start)
select
datepart(day, @Start) as StartDay,
datepart(day, @End) as EndDay,
datepart(week, @Start) as StartWeek,
datepart(week, @End) as EndWeek,
datepart(month, @Start) as StartMonth,
datepart(month, @End) as EndMonth
Here you can play around and uncomment one of the lines to see what is going on.
With the first Date, I intentionally set it to a Date and Time halfway through the day.
Adding 7 Seconds to it, did not even change the Minute, let alone the Day, Week or Month.
The second Date I picked right before midnight at the end of the Week.
Adding 7 Seconds to it, caused it to be the next Day and even the next Week.
And the third Date was right before midnight at the end of the Month, but not the end of the Week.
So adding 7 Seconds to it, I ended up in the next Month, but still in the same Week.
Do note that with the second Date you might get different results for the Week depending if your week goes from Monday to Sunday, or from Sunday to Saturday.
In other words, it depends on your DateFirst setting.
How to use it
So now you know how it works, you also know how it doesn't work.
And that means you need to choose what to use depending on the result you want to have.
If you want the difference between the Hours of the first and second Date, you can use the DateDiff Function.
But if you want the interval between the two Dates in Hours, you should use something like this:
declare @End datetime2 = dateadd(minute, 30, @Start)
select
@Start,
@End,
datediff(second, @Start, @End) / 60.0 / 60.0 as DiffInHours,
datediff(hour, @Start, @End) as DateDiffInHours
As you can see, I added 30 minutes for this one and the DiffInHours tells that this is half an hour, where the DateDiff tells it is 1 Hour.
It is not hard to imagine that not understanding the DateDiff Function might lead to errors in your Queries.
And by the way, as you can see I divided by 60.0, not 60.
That is because DateDiff returns an Integer.
And dividing an Integer by another Integer gives you another Integer, in this case the 0.5 would then be 0.
By explicitly dividing by a Float, I prevented that error.
Just check it out if you don't believe it. It is a common mistake.
That's it for today!
The DateDiff Function can be a usefull Function, but you need to have some understanding of how it works.
In many cases where I needed the interval in Hours or Days, I had to revert back to the DateDiff in Minutes (or even Seconds) and then calculate that to what I needed.
Especially when the given Dates are not the same Day, Week or Month.
And since not every new Week is a new Month, and not every Month is a new Week, the results you get might puzzle you.
But now you know why that is, so we can put the mysterious case of the DateDiff to rest.
Case Solved!
You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!