FB

SQL DATEDIFF Function Guide and Examples

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.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.