SQL DATEDIFF Function: Syntax and Date Difference Examples

In SQL Server 2025, DATEDIFF(datepart, startdate, enddate) returns a signed integer counting the specified datepart boundaries crossed. For example, DAY counts midnights and MONTH counts changes of calendar month. It does not necessarily count complete 24-hour days or complete months of elapsed time.

This distinction matters in invoice aging, employment reports, and publication-date comparisons. Choose the business rule first: a calendar-day difference, a completed anniversary, and an elapsed duration are different measurements. The main examples below use SQL Server 2025; the Oracle 26ai and other-database notes are labeled separately.

DATEDIFF(datepart, startdate, enddate)

• datepart selects the boundary to count, such as DAY, MONTH, or SECOND.
• startdate is the starting date or time expression.
• enddate is the ending date or time expression.

Write the datepart as an unquoted keyword: DATEDIFF(DAY, ...), not DATEDIFF('DAY', ...) or DATEDIFF("d", ...). SQL Server does not accept a variable in place of this keyword. Dates can come from typed variables, columns, or expressions.

• YEAR, QUARTER, and MONTH count calendar-period boundaries.
• DAY counts date changes; it does not exclude weekends or holidays.
• WEEK counts Sunday boundaries in SQL Server, regardless of SET DATEFIRST.
• HOUR, MINUTE, and SECOND count the corresponding time boundaries.
• MILLISECOND, MICROSECOND, and NANOSECOND support finer measurements, subject to the input type's precision and the result's range.

Use full datepart names in teaching and reporting code to make the intended unit clear. SQL Server also supports abbreviations and the DAYOFYEAR and WEEKDAY dateparts; WEEKDAY is not a business-day calculator.

These examples use explicit date values and unambiguous eight-digit date strings:

DECLARE @start_date date = '20240101';
DECLARE @end_date   date = '20240821';

SELECT DATEDIFF(DAY, @start_date, @end_date) AS days_between;
-- Expected: 233

SELECT DATEDIFF(MONTH,
                CAST('20230115' AS date),
                CAST('20240815' AS date)) AS months_between;
-- Expected: 19

The first result is a calendar-day difference. The second counts 19 changes of month. Matching day numbers make that second example intuitive, but matching day numbers are not a requirement for a month boundary to count.

DECLARE @start_time datetime2(7) = '2026-01-31T23:59:59.9000000';
DECLARE @end_time   datetime2(7) = '2026-02-01T00:00:00.1000000';

SELECT DATEDIFF(DAY, @start_time, @end_time) AS day_boundaries,
       DATEDIFF(MONTH, @start_time, @end_time) AS month_boundaries,
       DATEDIFF_BIG(MILLISECOND, @start_time, @end_time) AS milliseconds;
-- Expected: 1, 1, 200

Only 0.2 seconds separate these timestamps, but they cross both midnight and the start of February. Consequently, the day and month results are both 1. This is boundary counting, not rounding a duration up or down.

Likewise, DATEDIFF(YEAR, birth_date, @as_of_date) alone is not a reliable age calculation: it counts New Year boundaries even when the birthday has not occurred. Completed-year calculations need an anniversary check and an explicit policy for February 29 birthdays.

For a duration expressed in fractional seconds, a finer-grained count can be divided by a decimal divisor:

-- Using the datetime2 variables from the preceding example:
SELECT DATEDIFF_BIG(MICROSECOND, @start_time, @end_time)
       / 1000000.0 AS elapsed_seconds;
-- Expected numeric value: 0.2

This measures at microsecond granularity; it does not recover precision absent from the stored values. For events spanning time zones or daylight-saving changes, use a consistent time basis, such as UTC, rather than treating ambiguous local clock readings as elapsed time.

SELECT DATEDIFF(DAY,
                CAST('20261020' AS date),
                CAST('20261027' AS date)) AS forward_days,
       DATEDIFF(DAY,
                CAST('20261027' AS date),
                CAST('20261020' AS date)) AS reverse_days;
-- Expected: 7, -7

Read the arguments as “from start to end.” Reversing them reverses a nonzero result's sign. Two different timestamps can still return zero if no boundary of the selected type is crossed. A NULL datetime argument produces NULL; decide whether a missing date means “unknown” or whether a defined reporting cutoff should replace it.

Assume employees contains employee_id, hire_date, and nullable termination_date. If a missing termination date means the employee remains employed, use a fixed reporting date for repeatable results:

DECLARE @as_of_date date = '20261027';

SELECT employee_id,
       DATEDIFF(DAY, hire_date,
           COALESCE(termination_date, @as_of_date)) AS calendar_days
FROM employees;

This assumes recorded termination dates are actual departures, not scheduled future departures. With date-only values, the result is the number of days between those dates. It does not automatically count both endpoints as inclusive employment days. If that is required, define and apply the inclusive-count rule explicitly.

DECLARE @first_date date = '19980502';
DECLARE @last_date  date = '19980615';

SELECT DATEDIFF(DAY, @first_date, @last_date) AS days_between,
       DATEDIFF(WEEK, @first_date, @last_date) AS week_boundaries;
-- Expected: 44 days and 7 Sunday boundaries

Seven Sundays occur after May 2 and on or before June 15. The result does not mean 49 elapsed days. Multiplying a WEEK result by 7 therefore cannot reliably reconstruct a day difference. For complete seven-day periods between date-only values in forward order, use the day count divided by 7 with the intended integer-division rule.

Assume titles.title_id uniquely identifies each title. This query compares the publication dates of T05 and T06:

SELECT DATEDIFF(DAY, t1.pubdate, t2.pubdate) AS days_between
FROM titles AS t1
CROSS JOIN titles AS t2
WHERE t1.title_id = 'T05'
  AND t2.title_id = 'T06';

The uniqueness assumption gives at most one pair. If either title is absent, no row is returned. If either publication date is NULL, the difference is NULL.

A derived table can instead provide the earliest and latest known publication dates:

SELECT DATEDIFF(MONTH, p.first_pubdate, p.last_pubdate)
       AS publication_month_boundaries
FROM (
    SELECT MIN(pubdate) AS first_pubdate,
           MAX(pubdate) AS last_pubdate
    FROM titles
) AS p;

The aggregate result ignores NULL publication dates. If there are no known dates, both endpoints and the difference are NULL. The result describes the span of the catalog; it is not the average interval between publications.

DATEADD creates a shifted date; DATEDIFF counts boundaries between two dates. For a calendar-date filter, use explicit range endpoints rather than assuming a DATEDIFF expression means the intended time window:

DECLARE @report_date date = '20261027';

SELECT invoice_id, invoice_date
FROM invoices
WHERE invoice_date >= @report_date
  AND invoice_date < DATEADD(DAY, 1, @report_date);

This includes every timestamp on the reporting date and excludes midnight of the next date. Leaving the indexed column unwrapped makes an index range access possible; the optimizer still chooses the execution plan based on the schema and data.

Oracle 26ai: its documented DATEDIFF function also counts time-unit boundaries. Use Oracle date literals rather than copying SQL Server variable declarations:

-- Oracle AI Database 26ai
SELECT DATEDIFF(DAY, DATE '2026-10-20', DATE '2026-10-27')
       AS days_between
FROM dual;
-- Expected: 7

Oracle's optional fourth argument controls the start of a week, quarter, or year. For WEEK, the default depends on NLS_TERRITORY; specify 7 to select Sunday explicitly. Do not assume SQL Server's fixed week rule applies to Oracle. Check the installed Oracle release before using 26ai functions in older environments.

MySQL: DATEDIFF(end_date, start_date) takes two arguments, ignores time-of-day components, and returns days. PostgreSQL: subtracting one date from another returns an integer day difference; subtracting timestamps returns an interval. These are separate dialects, not interchangeable spellings of the SQL Server function.

Use DATEDIFF when the report needs a count of date or time boundaries. Choose an unquoted SQL Server datepart, put the start before the end, and define how NULL dates, inclusive endpoints, and time zones should be handled. Use DATEDIFF_BIG when the count can exceed the int range. For elapsed duration or completed anniversaries, write the calculation to match that business rule rather than interpreting a boundary count as the answer.

The next lesson explains DATEPART, which extracts a component from one date instead of comparing two dates.