The SQL Server DATEDIFF function lets you find the difference between two dates in a range of different units. Learn how to use it and see a range of examples in this guide.
Purpose of the SQL DATEDIFF Function
The purpose of the SQL DATEDIFF function is to return the count of the specified datepart boundaries crossed between the specified startdate and enddate.
What does this mean? Is this the same as the difference between two dates?
Yes, essentially it is the same, but in some situations it may not show the results you expect. We’ll see some examples later in this guide.
Syntax and Parameters
The syntax of the DATEDIFF function is:
DATEDIFF (datepart, startdate, enddate)It takes three parameters and they are all required.
datepart: This is the units that the DATEDIFF function calculates the difference between the two dates. It’s a specific keyword such as day, not a quoted string such as ‘day’. A list of possible values is shown below.
startdate: The date to start the difference calculation from. It can be any of the following data types:
- date
- datetime
- datetimeoffset
- datetime2
- smalldatetime
- time
enddate: The date to end the difference calculation from. It can be any of the following data types:
- date
- datetime
- datetimeoffset
- datetime2
- smalldatetime
- time
DATEDIFF DatePart Parameter Values
The first parameter of the DATEPART function is often called “datepart” and represents the units to report the difference between the two other date parameters.
The function accepts either the full name or the abbreviation below.
| Datepart Name | Datepart Abbreviation |
| year | yy, yyyy |
| quarter | qq, q |
| month | mm, m |
| dayofyear | dy, y |
| day | dd, d |
| week | wk, ww |
| hour | hh |
| minute | mi, n |
| second | ss, s |
| millisecond | ms |
| microsecond | mcs |
| nanosecond | ns |
DATEDIFF Return Values
The SQL DATEDIFF function returns an INT value.
You will get an error if the return value is larger than the maximum possible value for an INT: 2,147,483,647. For example, if you use a datepart of second, the maximum difference this function can return without an error is 68 years, 19 days, 3 hours, 14 minutes, and 7 seconds.
What Does It Mean By Boundaries?
In the MSDN documentation and other sites it mentions the concept of “datepart boundaries”. What does this mean?
It means that the DATEPART function returns the number of times the boundary between two units is crossed. This can have results that you are not expecting.
For example, this function returns 1.
SELECT DATEDIFF(second, '2019-12-31 23:59:59', '2020-01-01 00:00:00');A value of 1 is returned because the boundary of seconds is crossed once. The first date is on 59 seconds and the second date is on 0 seconds.
The same dates with a parameter of month is also 1:
SELECT DATEDIFF(month, '2019-12-31 23:59:59', '2020-01-01 00:00:00');This may not make sense initially, as there is only 1 second difference and not 1 month. But it returns 1 because the boundary of one month is being crossed: from December to January.
The same result will be achieved if a parameter of year is used: a year boundary is crossed, even though the actual difference in times is only one second.
This is just something to keep in mind.
Examples
Let’s take a look at some examples of the SQL DATEDIFF function.
Example 1: Days
This example finds the difference in days between the two dates.
SELECT DATEDIFF(day, '2019-12-04 17:10:41', '2019-12-14 08:09:55');Result:
10
Example 2: Years
This example shows the difference in years between two dates.
SELECT DATEDIFF(year, '2014-03-12 02:10:16', '2019-12-14 08:09:55');Result:
5
Example 3: Seconds
This example shows the difference in seconds between two dates.
SELECT DATEDIFF(second, '2019-12-04 17:10:41', '2019-12-14 08:09:55');Result:
831554
Example 4: Negative
This example demonstrates what happens if the startdate is after the enddate.
SELECT DATEDIFF(day, '2019-12-14 08:09:55', '2019-12-02 17:10:41');Result:
-12
Example 5: Use Current Date
This example uses the current date to find the difference between the current date and a specific date.
SELECT DATEDIFF(day, '2019-12-05 08:09:55', GETDATE());Result:
39
This function was run on 14 Jan which gives the result of 39.
Example 6: Date and No Time
This example shows what happens if we specify dates with no times.
SELECT DATEDIFF(minute, '2019-12-02', '2019-12-10');Result:
11520
A result is still shown, but the minute component of both dates is treated as 0 for this calculation.
Example 7: Use Database Columns
You can use database columns instead of specific dates for this function.
CREATE TABLE datetest (
date1 DATETIME,
date2 DATETIME
);
INSERT INTO datetest (date1, date2)
VALUES ('2019-12-05 08:09:55', '2019-12-19 17:10:41');
SELECT date1, date2, DATEDIFF(hour, date1, date2)
FROM datetest;Result:
| DATE1 | DATE2 | DIFF |
| 2019-12-05T08:09:55Z | 2019-12-19T17:10:41Z | 345 |
Example 8: Using MIN and MAX
You can use functions that return dates, such as MIN and MAX, inside DATEPART.
SELECT DATEPART(day, MIN(enrolment_date), MAX(enrolment_date)
FROM student;Result (assuming we have data populated in this table):
45
Conclusion
The DATEDIFF function can be used in SQL Server to find the difference between two dates, using a range of units from years all the way down to fractions of a second. It’s a handy function to remember.