Eric Campbell 5 min read Engineering

SQL - DATEDIFF() - Difference Between Dates

Date calculations sit at the heart of most business applications. You need them for aging reports, subscription management, SLA tracking, user retention analysis, and dozens of other features....

Key Insights

  • DATEDIFF() syntax varies significantly across databases—SQL Server uses three parameters with datepart, MySQL uses two, and PostgreSQL doesn’t have the function at all, requiring date subtraction instead.
  • Placing DATEDIFF() in WHERE clauses kills query performance by preventing index usage; restructure queries to compare against computed boundaries instead.
  • The function counts interval boundaries crossed, not complete intervals—DATEDIFF(YEAR, ‘2023-12-31’, ‘2024-01-01’) returns 1 despite being only one day apart.

Introduction to DATEDIFF()

Date calculations sit at the heart of most business applications. You need them for aging reports, subscription management, SLA tracking, user retention analysis, and dozens of other features. DATEDIFF() is the workhorse function that makes these calculations possible.

The function calculates the difference between two dates in a specified unit—days, months, years, hours, or other intervals. Simple concept, but the implementation details trip up developers constantly because every major database handles it differently.

Here’s the basic idea in SQL Server:

-- How many days between two dates?
SELECT DATEDIFF(DAY, '2024-01-01', '2024-03-15') AS days_difference;
-- Result: 74

That query returns 74 days. Straightforward enough. But try the same thing in MySQL or PostgreSQL, and you’ll get syntax errors. Each database took its own path with date arithmetic, and you need to know the differences to write portable code—or at least understand why your query fails when migrating databases.

Syntax and Parameters

SQL Server’s DATEDIFF() takes three parameters: the datepart (interval unit), start date, and end date.

DATEDIFF(datepart, startdate, enddate)

The datepart parameter accepts these common values:

  • YEAR or yy — Years
  • QUARTER or qq — Quarters
  • MONTH or mm — Months
  • DAY or dd — Days
  • WEEK or wk — Weeks
  • HOUR or hh — Hours
  • MINUTE or mi — Minutes
  • SECOND or ss — Seconds

The return value is always an integer representing how many of those intervals fit between the dates. Positive when enddate is later than startdate, negative when it’s earlier.

Here’s where it gets interesting—the same two dates produce wildly different results depending on your datepart:

DECLARE @start DATE = '2024-01-15';
DECLARE @end DATE = '2024-03-20';

SELECT 
    DATEDIFF(DAY, @start, @end) AS days,      -- 65
    DATEDIFF(WEEK, @start, @end) AS weeks,    -- 9
    DATEDIFF(MONTH, @start, @end) AS months,  -- 2
    DATEDIFF(YEAR, @start, @end) AS years;    -- 0

Notice that 65 days doesn’t equal 9 weeks times 7. That’s because DATEDIFF counts boundary crossings, not complete units. It counts how many times you cross from one week to the next, not how many full 7-day periods exist.

This boundary-crossing behavior creates a notorious gotcha:

-- These dates are ONE DAY apart
SELECT DATEDIFF(YEAR, '2023-12-31', '2024-01-01') AS years;
-- Result: 1

-- These dates are 364 days apart  
SELECT DATEDIFF(YEAR, '2024-01-01', '2024-12-31') AS years;
-- Result: 0

One day apart returns 1 year. Nearly a full year apart returns 0. The function counts year boundaries crossed, not elapsed time. Keep this in mind when calculating ages or tenure.

Common Use Cases

Calculating User Age

The boundary-crossing behavior means naive age calculations are wrong:

-- WRONG: This person isn't 1 year old yet
SELECT DATEDIFF(YEAR, '2024-06-15', '2025-01-10') AS age;
-- Returns 1, but they're only ~7 months old

-- CORRECT: Account for whether birthday has occurred this year
SELECT 
    DATEDIFF(YEAR, birthdate, GETDATE()) 
    - CASE 
        WHEN DATEADD(YEAR, DATEDIFF(YEAR, birthdate, GETDATE()), birthdate) > GETDATE() 
        THEN 1 
        ELSE 0 
      END AS accurate_age
FROM users;

Subscription Expiration

SELECT 
    user_id,
    subscription_end,
    DATEDIFF(DAY, GETDATE(), subscription_end) AS days_remaining,
    CASE 
        WHEN DATEDIFF(DAY, GETDATE(), subscription_end) < 0 THEN 'Expired'
        WHEN DATEDIFF(DAY, GETDATE(), subscription_end) <= 7 THEN 'Expiring Soon'
        ELSE 'Active'
    END AS status
FROM subscriptions;

SLA Compliance Tracking

SELECT 
    ticket_id,
    created_at,
    resolved_at,
    DATEDIFF(HOUR, created_at, resolved_at) AS resolution_hours,
    CASE 
        WHEN DATEDIFF(HOUR, created_at, resolved_at) <= 24 THEN 'Met SLA'
        ELSE 'Breached SLA'
    END AS sla_status
FROM support_tickets
WHERE resolved_at IS NOT NULL;

Database-Specific Implementations

This is where cross-database development gets painful. Here’s the same calculation across three major databases:

SQL Server

-- Days between order and shipment
SELECT 
    order_id,
    DATEDIFF(DAY, order_date, ship_date) AS days_to_ship
FROM orders;

MySQL

MySQL’s DATEDIFF() only calculates days—no datepart parameter:

-- MySQL: Only returns days, always
SELECT 
    order_id,
    DATEDIFF(ship_date, order_date) AS days_to_ship
FROM orders;

-- For other intervals, use TIMESTAMPDIFF()
SELECT 
    order_id,
    TIMESTAMPDIFF(HOUR, order_date, ship_date) AS hours_to_ship,
    TIMESTAMPDIFF(MONTH, order_date, ship_date) AS months_to_ship
FROM orders;

Note that MySQL reverses the parameter order—end date comes first.

PostgreSQL

PostgreSQL has no DATEDIFF() function. Use date subtraction or AGE():

-- PostgreSQL: Subtract dates directly for days
SELECT 
    order_id,
    ship_date - order_date AS days_to_ship
FROM orders;

-- For other intervals, use EXTRACT with AGE()
SELECT 
    order_id,
    EXTRACT(EPOCH FROM (ship_date - order_date)) / 3600 AS hours_to_ship,
    EXTRACT(YEAR FROM AGE(ship_date, order_date)) * 12 
        + EXTRACT(MONTH FROM AGE(ship_date, order_date)) AS months_to_ship
FROM orders;

-- Or use DATE_PART
SELECT 
    order_id,
    DATE_PART('day', ship_date - order_date) AS days_to_ship
FROM orders;

Working with NULL and Edge Cases

DATEDIFF() returns NULL if either input is NULL. This propagates through your calculations silently:

-- Returns NULL, not an error
SELECT DATEDIFF(DAY, '2024-01-01', NULL) AS result;

Wrap calculations with COALESCE for predictable behavior:

SELECT 
    order_id,
    COALESCE(
        DATEDIFF(DAY, order_date, ship_date),
        -1  -- Sentinel value indicating not yet shipped
    ) AS days_to_ship
FROM orders;

-- Or filter NULLs explicitly
SELECT 
    order_id,
    DATEDIFF(DAY, order_date, ship_date) AS days_to_ship
FROM orders
WHERE ship_date IS NOT NULL;

Timezone Considerations

DATEDIFF() operates on the stored values without timezone conversion. If you’re storing UTC timestamps but need calculations in local time, convert first:

-- SQL Server: Convert to local time before calculating
SELECT DATEDIFF(
    DAY, 
    created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time',
    GETDATE() AT TIME ZONE 'Eastern Standard Time'
) AS days_since_creation
FROM records;

Leap Years and Month Boundaries

February edge cases cause subtle bugs:

-- Both return 1 month, despite different day counts
SELECT DATEDIFF(MONTH, '2024-01-31', '2024-02-29') AS result1;  -- 1
SELECT DATEDIFF(MONTH, '2024-01-01', '2024-02-01') AS result1;  -- 1

If you need precise day-based month calculations, stick with days and divide.

Performance Considerations

Here’s the performance trap that catches most developers. This query looks reasonable:

-- NON-SARGABLE: Cannot use index on order_date
SELECT * 
FROM orders 
WHERE DATEDIFF(DAY, order_date, GETDATE()) <= 30;

The database must calculate DATEDIFF() for every row before filtering. With millions of rows and an index on order_date, that index sits unused. The query scans the entire table.

Rewrite to make it sargable (Search ARGument ABLE):

-- SARGABLE: Uses index on order_date
SELECT * 
FROM orders 
WHERE order_date >= DATEADD(DAY, -30, GETDATE());

Same logical result, but now the database can seek directly to matching rows using the index. On large tables, this transforms a 30-second query into a sub-second one.

The rule: keep columns naked on one side of the comparison. Move all calculations to the other side where they evaluate once, not per-row.

-- BAD: Function wraps the column
WHERE DATEDIFF(MONTH, created_at, GETDATE()) = 1

-- GOOD: Column compared against computed boundaries
WHERE created_at >= DATEADD(MONTH, -1, DATEADD(DAY, 1 - DAY(GETDATE()), GETDATE()))
  AND created_at < DATEADD(DAY, 1 - DAY(GETDATE()), GETDATE())

Practical Examples

Here’s a real-world report combining DATEDIFF with aggregations—average order fulfillment time grouped by month:

-- SQL Server: Monthly fulfillment performance report
SELECT 
    YEAR(order_date) AS order_year,
    MONTH(order_date) AS order_month,
    COUNT(*) AS total_orders,
    AVG(CAST(DATEDIFF(HOUR, order_date, ship_date) AS FLOAT)) AS avg_hours_to_ship,
    MIN(DATEDIFF(HOUR, order_date, ship_date)) AS fastest_hours,
    MAX(DATEDIFF(HOUR, order_date, ship_date)) AS slowest_hours,
    SUM(CASE 
        WHEN DATEDIFF(HOUR, order_date, ship_date) <= 24 THEN 1 
        ELSE 0 
    END) AS shipped_within_24h,
    CAST(SUM(CASE 
        WHEN DATEDIFF(HOUR, order_date, ship_date) <= 24 THEN 1 
        ELSE 0 
    END) AS FLOAT) / COUNT(*) * 100 AS pct_within_24h
FROM orders
WHERE ship_date IS NOT NULL
  AND order_date >= DATEADD(MONTH, -12, GETDATE())
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY order_year DESC, order_month DESC;

This query handles NULLs by filtering unshipped orders, uses DATEDIFF safely in SELECT (not WHERE), and provides actionable metrics for operations teams.

Date calculations are foundational. Master DATEDIFF() behavior across your target databases, respect the performance implications, and your date-based features will be both correct and fast.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.