datepart syntax 

The datepart parameter defines the unit of time to calculate the difference. SQL Server supports the following datepart values:

Note: MySQL supports only day as the unit of time for DATEDIFF().

• Quotation marks: Always use single quotes (') for date and time values.
• Positive and negative results: In SQL Server, the result is positive when enddate > startdate and negative otherwise.
• Error handling: If the result exceeds ±2,147,483,647, use DATEDIFF_BIG() in SQL Server.

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

In SQL Server:

SELECT DATEDIFF(year, '2022-12-31', '2024-06-01') AS years_difference;

Explanation: This calculates the difference in years between the two dates. Since the years 2023 and part of 2024 are counted, the result is 1.

In MySQL: 

SELECT DATEDIFF('2024-06-01', '2022-12-31') AS days_difference;

Explanation: MySQL only supports differences in days. The result is 518.

Example 2: Find the difference between two timestamps

SELECT 
    DATEDIFF(hour, '2023-12-31 00:00:00', '2023-12-31 23:59:59') AS hours_difference,
    DATEDIFF(minute, '2023-12-31 00:00:00', '2023-12-31 23:59:59') AS minutes_difference,
    DATEDIFF(second, '2023-12-31 00:00:00', '2023-12-31 23:59:59') AS seconds_difference;

Explanation:

• hours_difference returns 23 because the two timestamps are 23 hours apart.
• minutes_difference returns 1439, which is 23 hours * 60 minutes.
• seconds_difference returns 86,399, which is 1439 minutes * 60 seconds.

Example 3: Find the difference between two date columns

Suppose you have an orders table with order_date and shipped_date. Use DATEDIFF() to calculate the shipping time for each order.

SELECT 
    order_id,
    DATEDIFF(day, order_date, shipped_date) AS shipping_days 
FROM sales.orders;

Explanation: The query calculates the number of days it took to ship each order.

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 today_date,
    DATEDIFF(day, order_date, GETDATE()) AS days_from_order;

Explanation:

• GETDATE() retrieves the current date and time in SQL Server.
• The difference in days from the order_date to today is calculated.

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. 

Common Errors and Troubleshooting

When working with DATEDIFF(), it’s easy to run into issues related to syntax, argument order, or database-specific behavior. Below are common errors and how to address them:

1. Incorrect Quotation Marks

Problem: Using quotes around datepart or inconsistent quotation styles for date literals.

Solution: Always leave datepart unquoted and use single quotes for dates.

-- Incorrect
SELECT DATEDIFF('year', '2023-01-01', '2024-01-01');

-- Correct
SELECT DATEDIFF(year, '2023-01-01', '2024-01-01');

2. Unsupported datepart in MySQL

Problem: MySQL’s DATEDIFF() only supports day differences.

Solution: Use TIMESTAMPDIFF() for granular units like years or months.

-- Correct (MySQL)
SELECT TIMESTAMPDIFF(YEAR, '2023-01-01', '2024-01-01') AS years_difference; -- Returns 1

3. Argument Order Matters

Problem: Reversing startdate and enddate can lead to unexpected negative results.

Solution: Ensure startdate comes first. Use ABS() if only the magnitude matters.

-- Incorrect
SELECT DATEDIFF(day, '2023-01-05', '2023-01-01'); -- Returns -4

-- Correct
SELECT DATEDIFF(day, '2023-01-01', '2023-01-05'); -- Returns 4

4. Locale-Specific Date Formats

Problem: Ambiguous date formats (e.g., MM/DD/YYYY vs. DD/MM/YYYY) may cause errors.

Solution: Use the ISO 8601 standard (YYYY-MM-DD) to avoid ambiguity.

-- Correct
SELECT DATEDIFF(day, '2023-01-01', '2023-12-31'); -- Returns 364

5. Out-of-Range Values (SQL Server)

Problem: Large differences exceeding ±2,147,483,647 cause an overflow error.Solution: Use DATEDIFF_BIG() for large ranges.

-- Correct
SELECT DATEDIFF_BIG(day, '0001-01-01', '9999-12-31'); -- Returns 2,920,862,800

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