The SQL Server DATEDIFF system function is a very useful function for manipulating date information in your database.
In this very brief tutorial, we’ll discuss how the DATEDIFF function works and the key topics you should know when working with this function.
Here is what we’ll discuss:
- The syntax of the DATEDIFF function
- The ‘datepart’ values you can use in the DATEDIFF function
- Examples of using the DATEDIFF function
- Tips, tricks, and links
Let’s start from the top:
1. The syntax of the DATEDIFF function
The DATEDIFF function calculates the difference between two date values. The syntax for this function is very easy. It follows this format:
DATEDIFF(<datepart>, <startdate>, <enddate>)
Let’s talk about each part:
- datepart: This is a keyword representing what kind of date interval you want use in the difference calculation. For example, if you want to know how many weeks are between 1/1/2022 and 6/15/2022, you would specify a datepart of “week“. If you wanted to know the difference in days instead, the datepart would be the word “day“. There are many datepart values you can specify (week, day, month, hour, second, etc.). I will outline all possible keywords you can use as the datepart in the next section.
- startdate: This will be the starting date value you want to use in the difference calculation. Like in my example, we could say the startdate is 1/1/2022.
- enddate: This will be the ending date value you want to use in the difference calculation. In my example, the enddate is 6/15/2022.
The output is simply an integer representing how many dateparts are between your startdate and enddate values (the difference in days/weeks/hours/minutes between the two values).
Your input startdate or enddate values can be string literals or expressions (like the name of a column or another system function like GETDATE(), for example).
2. The ‘datepart’ values you can use in the DATEDIFF function
As I mentioned earlier, there are many different datepart values you can use in the DATEDIFF function. Here is the full list, pulled directly from the Microsoft documentation:
datepart Abbreviation
year yy or yyyy
quarter qq or q
month mm or m
dayofyear dy or y
day dd or d
week wk or ww
weekday dw or w
hour hh
minute mi or n
second ss or s
millisecond ms
microsecond mcs
nanosecond ns
As you can see, many of the datepart values can be abbreviated to something smaller. In my opinion, it’s better to just outline the whole word rather than an abbreviation. Some of the abbreviations aren’t very intuitive. For example, someone else looking at your code might not understand the abbreviation ‘n‘ is for minute.
I think outlining the whole word simply makes your code more readable and intuitive, for yourself and others!
3. Examples of using the DATEDIFF function
Let’s look at a few examples of using the DATEDIFF system function.
A simple example:
Let’s use DATEDIFF to calculate the difference in weeks between 1/1/2022 and 6/15/2022 like we discussed earlier. The syntax is very easy:
SELECT DATEDIFF(WEEK, '1/1/2022', '6/15/2022') as 'Difference in Weeks'
We can see there are 24 weeks between those two dates:
If we want to calculate the number of days instead, we just need to change the datepart value:
That looks correct.
Using other expressions in DATEDIFF:
Obviously, we aren’t limited to only using string literal values as our startdate or enddate values. We can use other expressions if we need to.
Let’s take a look at the an Orders table:
Let’s say we want to know the date the most recent order was made. It’s easy enough to figure that out by using the MAX aggregate function:
Maybe we want to calculate how many days have passed between when this most recent order was placed and the current date. Using DATEDIFF, we can easily get that information. I’ll use the SYSDATETIME() function, which returns the current date and time, as the enddate value (I’m writing this on 1/2/2022):
Pretty neat, eh?
There are plenty of other examples I could demonstrate, but the best way to understand this function is to just play around with it. Write your own queries using different start/end date values and different datepart values. See what happens! You’ll learn something new, guaranteed.
4. Tips, tricks, and links
Here is a list of tips and tricks you should know when working with the DATEDIFF system function:
Tip # 1: It’s possible for your resulting value to be negative
If your enddate value comes before your startdate value chronologically, the resulting number will be negative. Here’s a simple example:
If we start from the startdate, and need to go backwards to get to the endate, the difference will be a negative number. If instead we need to go forward to get to the enddate, the difference will be a positive number.
I suppose the word “backwards” can be synonymous with “negative“, and the word “forward” can be synonymous with “positive“.
Tip # 2: If a time is missing in either your startdate or enddate, it’s time defaults to 00:00:00
Let’s think about the following simple example, where we want to know how many hours have passed between 1/1/2022 and 1/3/2022:
Since we didn’t specify a time value for either date, both times default to midnight, 00:00:00. The difference in hours is easily calculated to 48.
But what if one of those dates does have a time?:
The number of hours changes accordingly. The point is: If you are calculating time differences, make sure both your startdate and enddate actually have a time value. The calculation might come out differently if one value has a time and the other does not.
Tip # 3: If a date is missing in either your startdate or enddate, it’s date defaults to 1900-01-01
This tip goes along the same idea as the last tip. If your startdate or enddate value only specifies a time, the date defaults to 1900-01-01.
Take a look at this strange result:
Since our startdate value does NOT specify a date, the date defaults to January 1st, 1900. The difference in days between then and 1/1/2022 appears to be 44560 days.
This probably isn’t what you wanted. Again, the point is: If you are calculating date differences, make sure both your startdate and enddate actually have a date value. The calculation might come out differently if one value has a date and the other does not.
Tip # 4: You should write the Year in four-digit format
You might have noticed that everywhere I specified a date, I always used the four-digit format for the year. It’s good to get into that habit. Think about if you specified a date of ‘3/1/22‘. Is that 2022? Or it is 1922?
Maybe 1822?
Help SQL Server by being explicit!
Links:
Here’s a link to the official Microsoft documentation on the DATEDIFF system function for further reading:
Next Steps:
If you found this tutorial helpful, you should also check out the DATEADD system function. This is another great function we can use for calculating date information in SQL Server. For example, using DATEADD, we can figure out what the date will be 43 days from today. The syntax would be:
SELECT DATEADD(DAY, 43, GETDATE()) AS '43 Days from Today'
Here’s the link to the full discussion to learn more:
SQL Server DATEADD System Function: Everything you need to know
Also, you might benefit from understanding the DATENAME system function. This is another helpful system function we can use when working with dates in SQL Server. Make sure you check it out:
DATENAME function: Explained
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
I hope you found this tutorial helpful. If you have any questions, leave a comment. Or better yet, send me an email!
