What is the difference between two dates using the DATEDIFF function (2023)


In this video, we will be finding out how many days are between two datetimes in SQL Server using the DATEDIFF function.
My SQL Server Udemy courses are:
70-461, 70-761 Querying Microsoft SQL Server with T-SQL: rebrand.ly/querying-microsoft-sql-server
98-364: Database Fundamentals (Microsoft SQL Server): rebrand.ly/database-fundamentals
70-462 SQL Server Database Administration (DBA): rebrand.ly/sql-server-dba
Microsoft SQL Server Reporting Services (SSRS): rebrand.ly/sql-server-ssrs
SQL Server Integration Services (SSIS): rebrand.ly/sql-server-ssis
SQL Server Analysis Services (SSAS): rebrand.ly/sql-server-ssas-mdx
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): rebrand.ly/microsoft-powerpivot-ssas-tabular-dax
My Excel for PC courses are:
Beginner to Excel - rebrand.ly/microsoft-excel-specialist
Intermediate to Expert Advanced - rebrand.ly/microsoft-excel-expert
Power Table, Get and Transform and Power Pivot - rebrand.ly/visualizing-data-excel
VBA macros for Excel - rebrand.ly/excel-vba-pc
The DATEDIFF function does allow you to find the difference between two date, datetime or time (or similar) fields. However, there are some quirks of this function, because of its integer logic. For example, if you have 30 days 23 hours, does it round down to 30 days, or round up to 31 days? And if you have 29 days 23 hours, what then? The answer is: it depends. Also, how can you get round the integer logic, and compute the exact number of days between the datetime fields?


In this video we're going to have a look at how you can get the difference between two date or date, time or date, time offsets.

So I've got here, two variables set up dat from and that two and you can see we go from the 3rd of april 2025 to the 3rd of may 2025.

So you can see going from here to there now in a lot of programming languages, for instance in excel.

You could just say something equivalent to that 2 minus that from to get the difference.

Unfortunately, that is not possible in sql server.

You can see the operand data type date time.

2 is invalid for the subtract operator.

In other words, you can't do it.

So what you have to do instead is use the function date diff so date, diff takes an interval in this case, maybe the day interval, so you could have it lowercase or uppercase, and it takes the starting date and the ending date, I want to say data include date, time and that sort of thing.

So if you have a look at this, we have 30 days between the 3rd of april and the 3rd of may, and that makes sense because those 30 days in april so so far so simple.

However, it does get more complicated with more quirks later on.

Now we can change this notice.

That day is not, for instance, in single quotation marks.

It is not a string that will not work.

It needs to be outside like day you could also have dd and you can also have d.

So, let's stick with day for the moment.

So what quirks can we see here? Well suppose I change this time element from being 12 34 to 13 34.

So what's the right answer now, we've added an extra hour.

Should it be still 30 or should it be 30 point and 124? Let's have a look and the answer is 30.

All you're doing is looking at the days we're not looking at the times.

So this could give some interesting answers.

For instance, suppose we went from midnight on the 3rd of april to almost midnight on the 3rd of may.

We still have the same answer 30.

and if we did it the other way around where we are now at 29 days or 1 hour, we still have the answer 30.

So if you want to have something with a more fractional answer, then we can't use day.

Instead, we should use something smaller, so alternatives today are month quarter so every three months year day of year, week hour minute, second millisecond, microsecond and nanosecond.

So suppose we have minutes.

Well, then the answer is 30 times 60 times 24.

So what we can do now is divide this by 24 and divide this by 60 and if you're familiar with sql division, you may notice that there is an error coming up, it's deliberate, so you can see that the answer is 30.

Now, let's change this, so we have an extra hour and the answer is still 30.

Let's change this, so we start at midnight and go to 11 o'clock in the evening.

The answer is still 30.

and let's change it so that this is the other way.

The answer is 29, so it's not exactly working as it did before.

Why is this? This is because we're using integer logic, we are getting the answer of 43 200 or whatever the answer is and dividing by 24 and dividing by 16 saying I need the answer to be an integer.

So if we want the answer to be fractional, we need one of these to be a decimal.

So that's all it is so now the answer is 30.00.

We add an hour.

The answer is 30.04 and similarly, if we change it to other things, 30.95 and we're going to have 29.

So that's what we need.

If you want a fractional answer, you need to have a smaller unit and then divide into that smaller unit to get days in this case now, if the seconds were changing, what we have in minute wouldn't be sufficient, so we'd have to use second.

So let's divide this by another 60.

So let's change this so that, instead of it being one hour difference it's now one hour and one second, you can see, we've got a fractional change, but what if we went further down, let's go into milliseconds and there is an important point coming up.

So let's take this down to millisecond.

So again, we've now got to divide by a thousand, and now we have a problem.

It resulted in an overflow.

In other words, this answer that we've got here is an int.

Specifically, it's a signed int, the maximum it can go up to is 2 billion, 147 million and we've gone beyond that we're going down to the millisecond level.

If that happens to you then there's a very easy remedy, just put underscore big and that will allow you to get not an int but a big hint and then most of your overflows shouldn't happen, but that will only happen really if you're taking more than about 25 days at the millisecond level.

So most of the time you won't have that.

So let's just get this back to the minute there we go right.

So that is what happens if we want the number of days and equally, we can get minutes and months and quarters exactly the same way.

Now there is something a bit odd with week.

So let's get week here, so we reset all of the time.

So time is not an issue.

So we have four weeks, and that makes sense there are 30 days between so 30 days from the third to the third.

So that's four weeks and we're doing integer logic yeah.

I can understand that now, let's change it instead of being from the third to the third, the third to the fourth okay.

So let's see how many days that is so now we are up to 31 days, okay, 31 days divided by 7, that's 4 and 3 7.

We truncate it into logic.

It's going to be 4.

If we have a look at weeks- and the answer is no, it isn't.

We now have five weeks and it's the same if we take it from the fifth of april to the fourth, which is a sunday.

We have five weeks, even though this is just 29 days.

So what's happening here well, what's happening here.

Is it's taking a week as being a week number, so this is, for example, a week number one week number two week number three.

So if we went from the fifth to the sixth because we're going over a week, then it will come up as one week, whereas if we're going from the sixth to the 19th, even though that is 13 days, then we will still have the one week because we start in one week and we only move to another week, so be very careful when you use week now.

One other thing to have a look at, and that is what happens if we have a look at offset so offset allows me to put in the time zone.

So again, let's reset this, so we go from the 3rd of april to the 3rd of may and let's put in a neutral timezone, as in greenwich mean time.

So this is london time in the winter, for instance.

So we can see that our formula still works and it works saying that we have got four weeks.

So we start off in this week.

We don't count this week.

So there's one week, two weeks, three weeks, four weeks now, let's change the time zone, so we are in minus 12 hour time zone.

So there aren't many places which are 12 hours behind gmt.

There's an island called midway, for instance, which is so now.

Let's see what the answer is, and the answer is now five weeks.

Why is this it's because this time, 12 34 is in gmt the next day it's the 4th of may at midnight 34, and therefore we have now gone beyond four weeks.

We've now gone into the fifth week, because we've gone to sunday, the fourth of may in gmt, so again be very careful.

When you have a look at offset date, time offset, we will be looking at what is it in gmt and then making the analysis.

So let's say both of these times were in midway time.

We would still have a difference of five weeks because we'll be starting now on the fourth of april and going all the way through to the fourth of may.

So that's one week, two weeks, three weeks, four weeks, five weeks, so hopefully these are some of the obstacles that you can have a look at for and make sure that you know how to code round them.

So if you want a fractional day, then don't take the answer a day, take it in hours or minutes and then divide by 24.0 if we're talking about hours and by 24.0, divided by 60, which is 1440.0.

If we're talking minutes and be extra careful, if we're looking at date, time offset.

Thank you for joining me in this video.

If you like it, then please click the like button and why not subscribe and click the bell next to it, so you'll be notified of any new videos.

Thank you for watching this and keep learning you.

Top Articles
Latest Posts
Article information

Author: Terrell Hackett

Last Updated: 12/14/2023

Views: 5409

Rating: 4.1 / 5 (52 voted)

Reviews: 91% of readers found this page helpful

Author information

Name: Terrell Hackett

Birthday: 1992-03-17

Address: Suite 453 459 Gibson Squares, East Adriane, AK 71925-5692

Phone: +21811810803470

Job: Chief Representative

Hobby: Board games, Rock climbing, Ghost hunting, Origami, Kabaddi, Mushroom hunting, Gaming

Introduction: My name is Terrell Hackett, I am a gleaming, brainy, courageous, helpful, healthy, cooperative, graceful person who loves writing and wants to share my knowledge and understanding with you.