Skip to main content

DATEDIFF() SQL FUNCTION

DATEDIFF() is one of the most widely used date data manipulation functions in SQL. Master it by reading this tutorial.
Updated Dec 6, 2024  · 3 min read

DATEDIFF() is one of the most widely used date and time manipulation functions in SQL. This tutorial will guide you through its usage, syntax, and key differences across popular SQL dialects, ensuring you master this powerful function.

What is the DATEDIFF() function?

The DATEDIFF() function calculates the difference between two date or timestamp values and returns the result in a specified unit, such as days, months, or years.

DATEDIFF Syntax Across SQL Dialects

The syntax for DATEDIFF() varies across SQL dialects. Below are examples for commonly used databases:

 

Here’s the fully updated and polished article with all the requested improvements and enhancements:


DATEDIFF() SQL Function

DATEDIFF() is one of the most widely used date and time manipulation functions in SQL. This tutorial will guide you through its usage, syntax, and key differences across popular SQL dialects, ensuring you master this powerful function.


What is the DATEDIFF() Function?

The DATEDIFF() function calculates the difference between two date or timestamp values and returns the result in a specified unit, such as days, months, or years.


DATEDIFF Syntax Across SQL Dialects

The syntax for DATEDIFF() varies across SQL dialects. Below are examples for commonly used databases:

SQL Server

DATEDIFF(datepart, startdate, enddate)
  • datepart: The unit of time (e.g., year, month, day).
  • startdate and enddate: The dates or timestamps to compare. The result is calculated as enddate - startdate.

MySQL

DATEDIFF(enddate, startdate)
  • Returns the difference in days only.

PostgreSQL

PostgreSQL does not have a DATEDIFF() function but provides similar functionality through:

  • The AGE() function:
SELECT AGE(enddate, startdate);
  • Direct subtraction of date values:
SELECT enddate - startdate;

Parameter

What is it?

datepart

A required parameter. This is the unit in which DATEDIFF() reports the difference between the startdate and enddate. See the following section for the different type of values it can hold. 

startdate

A required signifying a datetime value that denotes the start date. 

enddate

A required signifying a datetime value that denotes the end date. 

Associate Data Engineer in SQL

Gain practical knowledge in ETL, SQL, and data warehousing for data engineering.
Explore Track

datepart syntax 

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

Datepart Aliases
year yy, yyyy
quarter qq, q
month mm, m
day dd, d
hour hh
minute mi, n
second ss, s
millisecond ms

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.

SQL Upskilling for Beginners

Gain the SQL skills to interact with and query your data.

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

Learn more about SQL

Become SQL Certified

Prove your SQL skills are job-ready with a certification.

Travis Tang 's photo
Author
Travis Tang
LinkedIn

A data scientist at Tiktok and a Master's student at GeorgiaTech, I have an innate passion for data science, I've forged a unique career path that intertwines tech, risk, and continuous self-improvement. My tenure as a Data Scientist at Merchant Platform saw me mitigating risks and combating fraud using complex data models, saving thousands of dollars weekly. I've honed my craft by dedicating countless hours to self-learning, acquiring certificates from renowned institutions, and becoming proficient in topics such as Deep Learning, TensorFlow, and Social Network Analysis. On DataCamp and other educational platforms, I use this wealth of knowledge to inspire and educate others, forging new paths in the field of data science and proving that with determination and the right data, any risk can be turned into an opportunity.

Topics

Popular SQL Courses

course

Introduction to SQL

2 hr
881.7K
Learn how to create and query relational databases using SQL in just two hours.
See DetailsRight Arrow
Start Course
See MoreRight Arrow
Related

tutorial

FORMAT() SQL FUNCTION

FORMAT() is one of the most commonly used functions in SQL. Learn its main applications in this tutorial.
Travis Tang 's photo

Travis Tang

3 min

tutorial

Hacking Date Functions in SQLite

In this tutorial, learn how to use date functions in SQLite.
Hillary Green-Lerman's photo

Hillary Green-Lerman

3 min

tutorial

COUNT() SQL FUNCTION

COUNT() lets you count the number of rows that match certain conditions. Learn how to use it in this tutorial.
Travis Tang 's photo

Travis Tang

3 min

tutorial

COALESCE SQL Function

COALESCE() is one of the handiest functions in SQL. Read this tutorial to learn how to master it.
Travis Tang 's photo

Travis Tang

4 min

tutorial

Aggregate Functions in SQL

Learn how to use aggregate functions for summarizing results and gaining useful insights about data in SQL.
Sayak Paul's photo

Sayak Paul

9 min

tutorial

Understanding the LAG() Function in SQL: A Comprehensive Guide

Explore how the LAG() function allows you to access previous rows in your dataset, enabling time series analysis and comparisons of sequential observations.
Islam Salahuddin's photo

Islam Salahuddin

11 min

See MoreSee More