SQL DATEDIFF() Function: Syntax, Usage, and Examples

The DATEDIFF function calculates the difference between two dates and returns the result as an integer. It’s commonly used to determine how many days, months, or years have passed between two time values. DATEDIFF is widely supported, especially in SQL Server, MySQL (via TIMESTAMPDIFF), and other Transact-SQL-based systems.

How to Use DATEDIFF

The general DATEDIFF syntax depends on the database, but in SQL Server and Transact-SQL, the syntax looks like this:

DATEDIFF(datepart, start_date, end_date)

• datepart: The unit of time you want the difference in (day, month, year, etc.)
• start_date: The beginning of the period
• end_date: The end of the period

Basic Example

SELECT DATEDIFF(day, '2023-01-01', '2023-01-10') AS difference;
-- Output: 9

This shows that there are 9 full days between the two dates.

When to Use DATEDIFF

SQL DATEDIFF is useful in scenarios like:

• Calculating age from a birth date
• Finding overdue tasks
• Measuring time between transactions
• Generating reports with durations
• Filtering data by elapsed time

Typical Use Cases

Age Calculation

SELECT DATEDIFF(year, birth_date, GETDATE()) AS age
FROM employees;

This calculates each employee’s age in full years.

Order Fulfillment Time

SELECT order_id, DATEDIFF(day, order_date, delivery_date) AS days_to_deliver
FROM orders;

This is helpful for logistics tracking and performance reviews.

Time-Sensitive Alerts

SELECT task_id
FROM tasks
WHERE DATEDIFF(day, due_date, GETDATE()) > 0;

Returns all tasks past their due date.

Examples of DATEDIFF

Example 1: Difference in Days

SELECT DATEDIFF(day, '2023-04-01', '2023-04-10') AS days_apart;
-- Output: 9

This calculates the number of days between two fixed dates.

Example 2: Difference in Months

SELECT DATEDIFF(month, '2022-10-01', '2023-04-01') AS months_apart;
-- Output: 6

This shows that 6 full months passed between the dates.

Example 3: Using GETDATE() for Current Time

SELECT name, DATEDIFF(day, signup_date, GETDATE()) AS days_since_signup
FROM users;

This gives you a running counter of how long each user has been registered.

Example 4: DATEDIFF function in SQL Server with Time

You can also calculate time differences in hours, minutes, or seconds:

SELECT DATEDIFF(hour, '2023-04-01 08:00:00', '2023-04-01 14:00:00') AS hours_passed;
-- Output: 6

This is useful for server logs, uptime tracking, or shift management.

Learn More About DATEDIFF

DATEDIFF in SQL for Filtering

You can filter records based on a dynamic time window:

SELECT *
FROM logins
WHERE DATEDIFF(day, login_time, GETDATE()) <= 7;

This returns users who logged in during the past week.

Or filter by future dates:

SELECT *
FROM subscriptions
WHERE DATEDIFF(day, GETDATE(), expiration_date) <= 30;

Shows subscriptions expiring in the next 30 days.

DATEDIFF and Negative Values

DATEDIFF can return negative numbers if the start date is after the end date:

SELECT DATEDIFF(day, '2023-04-10', '2023-04-01') AS diff;
-- Output: -9

This is useful for checking how many days remain until a deadline.

DATEDIFF Transact Specifics

In Transact-SQL (used in SQL Server), datediff transact sql supports a variety of datepart values:

• year
• quarter
• month
• day
• hour
• minute
• second
• millisecond
• microsecond
• nanosecond

Example with minutes:

SELECT DATEDIFF(minute, '2023-04-01 10:00', '2023-04-01 10:45') AS duration;
-- Output: 45

DATEDIFF and Zero Output

If both dates fall within the same time unit (e.g., the same day), and you're counting by a larger unit (like day or month), the result may be zero:

SELECT DATEDIFF(day, '2023-04-01 01:00', '2023-04-01 23:59') AS diff;
-- Output: 0

This is because DATEDIFF counts the number of boundaries crossed, not the exact difference.

Using DATEDIFF with Aliases and CASE

You can build conditional queries using DATEDIFF:

SELECT task_id,
  CASE
    WHEN DATEDIFF(day, due_date, GETDATE()) > 0 THEN 'Overdue'
    ELSE 'On Time'
  END AS status
FROM tasks;

This tags each task with a status based on how far past its due date it is.

Nesting DATEDIFF Inside Other Functions

You can wrap DATEDIFF inside other functions like ABS() to get absolute differences:

SELECT ABS(DATEDIFF(day, hire_date, termination_date)) AS total_days;

Or use it in ORDER BY to sort by how recent or old something is:

SELECT name, DATEDIFF(day, created_at, GETDATE()) AS age
FROM products
ORDER BY age DESC;

DATEDIFF vs TIMESTAMPDIFF

Some databases like MySQL don’t use DATEDIFF in the same way. Instead, they use TIMESTAMPDIFF():

SELECT TIMESTAMPDIFF(DAY, '2023-01-01', '2023-01-10') AS diff;

This acts similarly to sql datediff in SQL Server. Always check your database’s specific implementation.

Timezones and DATEDIFF

If your application deals with different time zones, remember that DATEDIFF does not automatically adjust for time zone offsets. You’ll need to handle time zone conversions explicitly before comparing times.

The SQL DATEDIFF function is a simple yet powerful tool for measuring time-based differences in your data. From tracking user activity to calculating contract durations or overdue tasks, it helps developers and analysts work efficiently with time-sensitive information. Mastering DATEDIFF allows you to write cleaner queries, build smarter alerts, and drive insights across any date-based data set.