Supported Editors

Free Tools

Resources

Blog

Docs

Pricing

Book a Demo

B

Get Free Extension

S

Get Sherloq Free

Back to Library

DATEDIFF() in SQL

Calculates the difference between two dates in specified interval units.

Syntax

DATEDIFF(interval_type, start_date, end_date) 
-- Some databases use: DATE_DIFF() or end_date - start_date
Return type
INTEGER

DATEDIFF() Function Example

SELECT DATEDIFF(day, order_date, delivery_date) FROM orders; 
-- Output: 5 (difference of 5 days between dates)

What is DATEDIFF() in SQL?

The DATEDIFF() function in SQL calculates the difference between two dates in a specified time interval (such as days, months, or years). It is commonly used for age calculations, duration measurements, and event tracking.

This function is primarily supported in SQL Server. For other databases:

  • MySQL & PostgreSQL use DATEDIFF(start_date, end_date), but only return the difference in days.

  • Oracle calculates the difference directly by subtracting two dates.

Syntax (SQL Server)

Output: 364 (number of days between the two dates).
The function supports multiple interval units, such as:
  • YEAR → Difference in years.
  • MONTH → Difference in months.
  • DAY → Difference in days.
  • HOUR, MINUTE, SECOND for time-based calculations.
It is widely used in reporting, scheduling, and historical data analysis.
Parameters:
  • interval_type: Unit to measure difference (year, month, day, hour, minute, second)
  • start_date: Beginning date
  • end_date: Ending date
Example Use Cases:
-- Calculate order processing time
SELECT order_id, DATEDIFF(hour, order_time, shipping_time) AS processing_hours FROM orders; 
-- Output:
order_id    processing_hours
--------    ----------------
ORD001      5
ORD002      7
ORD003      22
ORD004      5
ORD005      20
-- Find customer age 
SELECT DATEDIFF(year, birth_date, CURRENT_DATE) AS age FROM customers; 
-- Output:
age
---
39
32
46
29
-- Calculate project duration
SELECT project_id, DATEDIFF(day, start_date, end_date) AS duration_days FROM projects;
-- Output:
project_id    duration_days
----------    -------------
PROJ001       75
PROJ002       104
PROJ003       112
PROJ004       136
PROJ005       90
Notes:
  • Behavior: Returns NULL if either date is NULL
  • Performance Considerations: Integer arithmetic is efficient
  • Version Info: Syntax varies by DBMS
  • Deprecated/Recommended Alternatives: None
Error Handling:
  • Error: Returns NULL for invalid dates
  • Recommendation: Validate date inputs before calculation
Supported Databases:
DBMS
Function / Syntax
Example
Behavior with NULL
MySQL
DATEDIFF(), TIMESTAMPDIFF()
DATEDIFF(date1, date2)
Returns NULL
PostgreSQL
AGE(), - operator
date1 - date2
Returns NULL
SQL Server
DATEDIFF()
DATEDIFF(day, date1, date2)
Returns NULL
SQLite
julianday()
julianday(date2) - julianday(date1)
Returns NULL
BigQuery
DATE_DIFF(), TIMESTAMP_DIFF()
DATE_DIFF(date2, date1, DAY)
Returns NULL
Snowflake
DATEDIFF()
DATEDIFF(day, date1, date2)
Returns NULL
Athena
DATE_DIFF()
DATE_DIFF(day, date1, date2)
Returns NULL
Related Functions:
DATEADD()
Adds or subtracts a specified time interval from a date value.
GETDATE()
Returns the current date and time of the database server.
DATEFROMPARTS()
Creates a date value from specified year, month, and day integers.
Creates a date value from specified year, month, and day integers.
Creates a date value from specified year, month, and day integers.
Creates a date value from specified year, month, and day integers.
DATEFORMAT()
Converts a date/timestamp expression into a specified string format.
Converts a date/timestamp expression into a specified string format.
Converts a date/timestamp expression into a specified string format.
Converts a date/timestamp expression into a specified string format.
EOMONTH()
Returns the last day of the month for a specified date with an optional month offset.
Returns the last day of the month for a specified date with an optional month offset.
Returns the last day of the month for a specified date with an optional month offset.
Returns the last day of the month for a specified date with an optional month offset.
DAY()
Extracts the day of the month (1-31) from a date or timestamp expression.
Extracts the day of the month (1-31) from a date or timestamp expression.
Extracts the day of the month (1-31) from a date or timestamp expression.
Extracts the day of the month (1-31) from a date or timestamp expression.
MONTH()
Extracts the month value (1-12) from a date or timestamp expression.
Extracts the month value (1-12) from a date or timestamp expression.
Extracts the month value (1-12) from a date or timestamp expression.
Extracts the month value (1-12) from a date or timestamp expression.
YEAR()
Extracts the year value from a date or timestamp expression.
Extracts the year value from a date or timestamp expression.
Extracts the year value from a date or timestamp expression.
Extracts the year value from a date or timestamp expression.
DATEDIFF()
Calculates the difference between two dates in specified interval units.
Calculates the difference between two dates in specified interval units.
Calculates the difference between two dates in specified interval units.
Calculates the difference between two dates in specified interval units.
One place for all your queries, 
directly on your SQL editor
Get Free Extension
Book a Demo
B
Get Sherloq Free
S
Explore
Features
Supported Editors
AppsFlyer Case Study
Weski Case Study
Resources
Pricing
Blog
Docs
Partners
Community
Referral Program
Free Tools
AI SQL Organizer
SQL Visual Tool (ERD)
SQL Greeting Generator
Legal
Privacy policy
Terms & Conditions
Security
Book a Demo
B
Get Sherloq Free
S
Explore
Features
Supported Editors
AppsFlyer Case Study
Weski Case Study
Resources
Pricing
Blog
Docs
Partners
Community
Referral Program
Free Tools
AI SQL Organizer
SQL Visual Tool (ERD)
SQL Greeting Generator
Legal
Privacy policy
Terms & Conditions
Security
Book a Demo
B
Get Sherloq Free
S
Explore
Features
Supported Editors
AppsFlyer Case Study
Weski Case Study
Resources
Pricing
Blog
Docs
Partners
Community
Referral Program
Free Tools
AI SQL Organizer
SQL Visual Tool (ERD)
SQL Greeting Generator
Legal
Privacy policy
Terms & Conditions
Security