DATEDIFF() SQL FUNCTION

What is the DATEDIFF() function?

The DATEDIFF() function returns the interval between two timestamps or date values in a specified unit. 

DATEDIFF() syntax

DATEDIFF(datepart, startdate, enddate)

datepart syntax 

The datepart parameter can take in a lot of values, including the following 

• Year: year, y, yy, or yyyy
• Quarter: quarter, qq, q
• Month: month, mm, m
• Day: dayofyear, day, dd, d
• Week: week, wk, ww
• Hour: hour, hh
• Minute: minute, mi, n
• Second: second, ss, s
• Millisecond: millisecond, ms
• Microsecond: microsecond, mcs
• Nanosecond: nanosecond, ns

 Note that datepart should not be enclosed by quotes. For example, the following syntax is correct:

DATEDIFF(year, ‘2022-01-01', ‘2022-01-02')

The following is incorrect.

DATEDIFF(‘year', ‘2022-01-01', ‘2022-01-02')

DATEDIFF() examples

When working with DATEDIFF(), the results can either be positive or negative values. If enddate is later than startdate, then DATEDIFF() returns a positive value. If enddate is earlier than startdate, DATEDIFF() returns a negative value. 

Example 1: Find the difference between two dates

SELECT DATEDIFF(timepart, ‘2022-12-31', ‘2024-06-01')

Example 2: Find the difference between two timestamps

SELECT DATEDIFF(datepart, '2023-12-31 00:00:00, '2023-12-31 00:59:59')

Example 3: Find the difference between two date columns

Consider the table  sales.orders, which contains these columns:

• order_date (when the order is placed) 
• shipped_date (when the order is shipped)

To track the shipping turnaround time, we can use the DATEDIFF() function.

SELECT
    order_id,
    order_date,
    shipped_date,
    DATEDIFF(day, order_date, shipped_date) order_to_ship_days
FROM sales.orders

Example 4. Find the interval between today's date and a column

Using the sales.orders table as in Example 3, we can use DATEDIFF() to find the interval between today's date and the date on which an order is placed.

SELECT
   order_id,
   order_date,
   GETDATE() AS todays_date,
   DATEDIFF(day, order_date, GETDATE()) AS order_to_today -- interval between order_date with today's date
FROM sales.orders

Additional Notes

The DATEDIFF() function returns an error if the result is out of range (i.e. the value is larger than +2,147,483,647 or smaller than -2,147,483,647). In this case, the function DATEDIFF_BIG() is appropriate. 

Technical requirements

Works in SQL Server (starting with 2008), Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse

See also

• COALESCE() SQL Function
• FORMAT() SQL Function
• INSERT INTO SQL Function
• COUNT() SQL Function

Learn more about SQL

• Learn SQL hub
• SQL Tutorials
• SQL Cheat Sheets