Course
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
andenddate
: The dates or timestamps to compare. The result is calculated asenddate - 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? |
|
A required parameter. This is the unit in which |
|
A required signifying a datetime value that denotes the start date. |
|
A required signifying a datetime value that denotes the end date. |
Associate Data Engineer in SQL
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
returns23
because the two timestamps are 23 hours apart.minutes_difference
returns1439
, which is23 hours * 60 minutes
.seconds_difference
returns86,399
, which is1439 minutes * 60 seconds
.
SQL Upskilling for Beginners
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

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.