Skip to main content
Log inGet a demo
Back to SQL Dictionary
Date functions

SQL DATEDIFF

What is SQL DATEDIFF?

The SQL DATEDIFF function is used to calculate the difference between two date or timestamp values, resulting in an integer that represents the time span between them. This function allows you to determine the duration between two dates in various units, such as days, months, years, hours, minutes, or seconds.

When you would use it

You would use the DATEDIFF function when you need to:

  1. Calculate Time Intervals: Determine the time span between two dates or timestamps. This can be useful for tasks like calculating the age of individuals, finding the duration of events, or measuring the time taken for operations.

  2. Date-Based Filtering: Filter records based on specific date or time criteria. For instance, you may want to retrieve records with a duration longer than a specified number of days.

  3. Data Analysis: Perform data analysis or reporting tasks that involve time intervals. This can include calculating average time durations or identifying trends in time-related data.

Syntax

The syntax for the DATEDIFF function may vary slightly depending on the database system, but a general representation is as follows:

DATEDIFF(unit, start_date, end_date)
  • unit: The unit of time for expressing the difference (e.g., DAY, HOUR, MINUTE, SECOND).
  • start_date: The starting date or timestamp.
  • end_date: The ending date or timestamp.

Parameter values

  • unit: The unit of time you want to use for the difference calculation. Common values include DAY, HOUR, MINUTE, SECOND, etc.
  • start_date: The earlier date or timestamp.
  • end_date: The later date or timestamp.

Example query

Here's an example SQL query that calculates the number of days between the "start_date" and "end_date" in a table named "events":

SELECT
    event_name,
    start_date,
    end_date,
    DATEDIFF(DAY, start_date, end_date) AS duration_in_days
FROM events;

Example table response

The example query calculates the duration in days between the "start_date" and "end_date" for events in the "events" table. The resulting table response will look like this:

event_namestart_dateend_dateduration_in_days
Event A2023-10-012023-10-054
Event B2023-09-152023-09-194
Event C2023-08-252023-08-283

Use cases

  1. Calculate Time Durations: To find the time span between two date or timestamp values, enabling you to determine durations for various purposes, such as event planning, performance analysis, or age calculation.

  2. Date-Based Filtering: To filter and retrieve records based on specific date or time criteria, allowing you to fetch data with durations within a specified range.

  3. Data Analysis: For performing data analysis tasks involving time intervals, like calculating averages, identifying trends, or generating reports that include time-based metrics.

SQL Languages Availability

The DATEDIFF function is available in various SQL-based relational database management systems (RDBMS), but the specific function name, syntax, and supported units of time may vary between systems. Here are some examples of its availability:

  • SQL Server: DATEDIFF(unit, start_date, end_date)
  • MySQL: DATEDIFF(end_date, start_date)
  • PostgreSQL: EXTRACT(unit FROM age(end_date, start_date))
  • Oracle Database: end_date - start_date
  • IBM Db2: DAYS(end_date) - DAYS(start_date)
  • SQLite: The function is not standardized, but you can perform similar calculations using date and time functions.

Refer to the documentation of your specific RDBMS for precise usage details and variations.

Related

SQL DATE_ADD

SQL DATE_FORMAT

SQL UNIQUE

Ready to put your SQL knowledge to work?

Practice writing SQL to call data from the warehouse and sync it into Google Sheets in this 5 minute interactive demo.

Hightouch Audiences user interface.