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

Data Manipulation in SQL

Beginner
4 hours
155,991
Master the complex SQL queries necessary to answer a wide variety of data science questions and prepare robust data sets for analysis in PostgreSQL.
See DetailsRight Arrow
Start Course

Introduction to SQL

Beginner
2 hours
114,628
Learn how to create and query relational databases using SQL in just two hours.

Intermediate SQL

Beginner
4 hours
39,076
Accompanied at every step with hands-on practice queries, this course teaches you everything you need to know to analyze data using your own SQL code today!
See all coursesRight Arrow
Related

How to Become a Data Analyst in 2023: 5 Steps to Start Your Career

Learn how to become a data analyst and discover everything you need to know about launching your career, including the skills you need and how to learn them.
Elena Kosourova 's photo

Elena Kosourova

18 min

What is SQL Used For? 7 Top SQL Uses

Discover the uses of SQL in industries and specific jobs. Plus, learn why the SQL language is so versatile and in demand.
Natassha Selvaraj's photo

Natassha Selvaraj

11 min

Sports Analytics: How Different Sports Use Data Analytics

Discover how sports analytics works and how different sports use data to provide meaningful insights. Plus, discover what it takes to become a sports data analyst.
Kurtis Pykes 's photo

Kurtis Pykes

13 min

SQL Window Functions Cheat Sheet

With this SQL Window Functions cheat sheet, you'll have a handy reference guide to the various types of window functions in SQL.
DataCamp Team's photo

DataCamp Team

10 min

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

How to Write a Bash Script: A Simple Bash Scripting Tutorial

Discover the basics of bash scripting and learn how to write a bash script.
Kurtis Pykes 's photo

Kurtis Pykes

5 min

See MoreSee More