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.
Oct 2022

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)

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. 

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')

Query

Output

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

1

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

2

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

6

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

153

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

21

Example 2: Find the difference between two timestamps

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

Query

Output

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

0

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

59

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

3599

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

3599000

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

order_id

order_date

shipped_date

order_to_ship_days

1

2016-01-01T00:00:00.000Z

2016-01-03T00:00:00.000Z

2

2

2016-01-01T00:00:00.000Z

2016-01-03T00:00:00.000Z

2

3

2016-01-02T00:00:00.000Z

2016-01-03T00:00:00.000Z

1

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

order_id

order_date

shipped_date

order_to_ship_days

1

2016-01-01T00:00:00.000Z

2022-10-09T02:47:00.403Z

2473

2

2016-01-01T00:00:00.000Z

2022-10-09T02:47:00.403Z

2473

3

2016-01-02T00:00:00.000Z

2022-10-09T02:47:00.403Z

2472

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

Learn more about SQL

Popular SQL Courses

Introduction to SQL

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

20 Data Analytics Projects for All Levels

Explore our list of data analytics projects for beginners, final-year students, and professionals. The list consists of guided/unguided projects and tutorials with source code.
Abid Ali Awan's photo

Abid Ali Awan

17 min

Gary Wolf- Dataframed 119.png

Data-Driven Thinking for Everyday Life

Gary Wolf talks about what The Quantified Self is, why self-tracking projects can be life-changing, how to get started with self-tracking, and how to connect with others.

Richie Cotton's photo

Richie Cotton

55 min

Reshaping Data with pandas in Python

Pandas DataFrames are commonly used in Python for data analysis, with observations containing values or variables related to a single object and variables representing attributes across all observations.
Richie Cotton's photo

Richie Cotton

Reshaping Data with tidyr in R

In this cheat sheet, you will learn how to reshape data with tidyr. From separating and combining columns, to dealing with missing data, you'll get the download on how to manipulate data in R.
Richie Cotton's photo

Richie Cotton

6 min

Data Quality Dimensions Cheat Sheet

In this cheat sheet, you'll learn about data quality dimensions, allowing you to ensure that your data is fit for purpose.
Joe Franklin's photo

Joe Franklin

3 min

Top Techniques to Handle Missing Values Every Data Scientist Should Know

Explore various techniques to efficiently handle missing values and their implementations in Python.
Zoumana Keita 's photo

Zoumana Keita

15 min

See MoreSee More