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

Popular SQL Courses

Introduction to SQL

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

What is Data Analysis? An Expert Guide With Examples

Explore the world of data analysis with our comprehensive guide. Learn about its importance, process, types, techniques, tools, and top careers in 2023
Matt Crabtree's photo

Matt Crabtree

15 min

10 Portfolio-Ready SQL Projects for All Levels

Select your first—or next—SQL project to practice your current SQL skills, develop new ones, and create an outstanding professional portfolio.
Elena Kosourova's photo

Elena Kosourova

11 min

What is Microsoft Fabric?

Discover how Microsoft Fabric revolutionizes data analytics and learn about how its core features empower businesses to make data-driven decisions.
Kurtis Pykes 's photo

Kurtis Pykes

10 min

How to Pass the PL-300 Power BI Certification

Learn how to pass the PL-300 certification exam for Power BI—one of the world’s most popular business intelligence tools—in this convenient cheat sheet!
Richie Cotton's photo

Richie Cotton

7 min

QUALIFY: The SQL Filtering Statement You Never Knew You Needed

Learn about the SQL QUALIFY clause, an essential yet lesser-known filtering method in SQL. Understand its syntax, uses, and how it differs from other SQL filtering methods.
Kurtis Pykes 's photo

Kurtis Pykes

8 min

Performance and Scalability Unleashed: Mastering Single Table Database Design with DynamoDB

One table to rule them all: simplify, scale, and supercharge your NoSQL database!
Gary Alway's photo

Gary Alway

16 min

See MoreSee More