Skip to main content
HomeTutorialsSQL

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  · 3 min read

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

Topics

Popular SQL Courses

Course

Introduction to SQL

2 hr
646.4K
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

INSERT INTO SQL FUNCTION

INSERT INTO lets you add data to your tables. Learn how to use it in this tutorial.
Travis Tang 's photo

Travis Tang

3 min

See MoreSee More