DATEDIFF in SQL: date boundaries, age, and engine differences

Two minutes can produce a result of one day, and crossing from December into January can produce one year. In SQL Server, that is valid DATEDIFF behaviour: the function counts boundaries of the selected date part. Trouble begins when I label that result as a completed duration or a person’s age.

This guide separates four database engines and several different questions about time. All data is synthetic, and reference dates are fixed, so the result will not change tomorrow. PostgreSQL examples were executed on version 17.11, and SQLite examples on version 3.53.4 through node:sqlite in Node 24.22.0. SQL Server and MySQL code was checked against documentation; those two engines were not run in the editorial environment. Execution in PostgreSQL or SQLite does not validate another dialect.

Choose the meaning of the difference first

Before typing a function name, I write the question in ordinary language. Am I counting calendar days between dates? Seconds taken by a process? Completed years since a birthday? Or changes of calendar month between events? Each quantity can be useful, but they are not interchangeable.

An event running from 23:59 to 00:01 lasts two minutes and crosses midnight. In calendar terms, it touches two dates. In terms of completed 24-hour periods, it lasts less than one. A column called days without a definition leaves too much interpretation to the reader.

This table helps select an operation; it is not a common SQL signature. PostgreSQL and SQLite have no built-in DATEDIFF matching SQL Server syntax. When moving a query between engines, I change both its expression and the tests of its intended meaning.

I separately establish whether the reader wants a date difference or a count of dates in a period. February 1 to February 3 has a difference of two days, but a list including both endpoints contains three dates. Adding one can implement that second definition when the dates are in the correct order. I do not add it automatically to every result, particularly with timestamps or an interval that excludes its end.

None of the shown operations directly counts business days while accounting for holidays and an organisation’s schedule. For that question, I would prepare a calendar identifying working dates and count the relevant records. Waiting time measured only during a team’s operating hours likewise needs a schedule. Switching the unit from days to hours does not introduce breaks, weekends, or local exceptions into the query. I clarify this before building the metric, rather than correcting an already published report.

SQL Server: counting calendar boundaries

SQL Server takes a date part, a start, and an end. I write the date part as a keyword, such as day, without quotation marks. The expected results of the following four expressions are 152, 1, 1, and 1.

-- example: sqlserver-boundaries
SELECT
    DATEDIFF(day, '20240101', '20240601') AS days,
    DATEDIFF(year, '20251231', '20260101') AS year_boundaries,
    DATEDIFF(month, '20260131', '20260201') AS month_boundaries,
    DATEDIFF(day, '2026-02-01T23:59:00', '2026-02-02T00:01:00') AS day_boundaries;

The first example includes the leap year 2024. January through May contribute 31 + 29 + 31 + 30 + 31 days, totalling 152. The other expressions deliberately show cases where boundary counts are easy to confuse with duration.

December 31 to January 1 crosses one year boundary. January 31 to February 1 crosses one month boundary. 23:59 to 00:01 crosses one day boundary. None implies that a full year, month, or 24-hour day has elapsed. This definition is confirmed by the DATEDIFF documentation.

For a short process, I select a unit appropriate to the required precision. A smaller unit will not repair an unsuitable column type or recover lost fractions of a second, however. If the input was rounded earlier, the function cannot reconstruct that detail. The output type and its range matter too.

Age in SQL Server: check the anniversary

For someone born on June 15, 1990, DATEDIFF(year, ...) alone returns 36 as early as January 1, 2026. Before the June birthday, the completed age is 35. Here I calculate the boundary count, construct the anniversary, and subtract one if it has not yet occurred.

-- example: sqlserver-age
DECLARE @birth date = '19900615';
DECLARE @as_of date = '20260614';
SELECT DATEDIFF(year, @birth, @as_of)
       - CASE WHEN DATEADD(year, DATEDIFF(year, @birth, @as_of), @birth) > @as_of
              THEN 1 ELSE 0 END AS completed_years;

The expected result on June 14 is 35. Changing @as_of to June 15 should produce 36. I use date because this task works with calendar dates rather than the time of birth. A birth date later than the reference date requires separate invalid-data handling; this formula is not presented as a universal calculation of negative ages.

February 29 requires a rule for the anniversary in a non-leap year. The variation based on DATEADD uses the last day of February. That is an explicit calculation convention to agree with the metric’s definition. If the process requires March 1, I change the rule and its test. A function’s behaviour does not automatically define every business interpretation of age or tenure.

Complete months require an end-of-month rule

A similar adjustment works for months if anniversaries follow DATEADD. For January 31 to February 28, 2026, this variation expects 1.

-- example: sqlserver-months
DECLARE @start date = '20260131';
DECLARE @finish date = '20260228';
SELECT DATEDIFF(month, @start, @finish)
       - CASE WHEN DATEADD(month, DATEDIFF(month, @start, @finish), @start) > @finish
              THEN 1 ELSE 0 END AS anniversary_months;

The reason is that the last day of the shorter month is accepted as the anniversary. January 15 to February 14 would produce 0, and January 15 to February 15 would produce 1. Here I assume nonmissing dates and an end no earlier than the start.

This is one possible definition of a complete month. Later, MySQL and PostgreSQL AGE will produce 0 for January 31 to February 28. The difference follows from the rule, so comparing numbers without their definitions can produce a misleading bug report. Shorter-month behaviour is described in DATEADD.

I do not divide days by 30 when the question concerns calendar months. That quotient can be a separate conventional metric, provided it is named accordingly. January, February, and March have different lengths, and a leap year adds another variation.

DATEDIFF_BIG: a larger result range

DATEDIFF supports seconds and milliseconds. DATEDIFF_BIG is useful when the boundary count exceeds the range of int; its purpose is not to unlock units supposedly unavailable in the ordinary function.

-- example: sqlserver-big
SELECT
    DATEDIFF(second, '2026-06-01T10:00:00', '2026-06-01T10:05:30') AS seconds,
    DATEDIFF_BIG(millisecond, '20260101', '20260131') AS milliseconds;

The expected results are 330 seconds and 2592000000 milliseconds. I check the first as 5 × 60 + 30 and the second as 30 × 24 × 60 × 60 × 1000. The second exceeds the maximum positive int, so ordinary DATEDIFF(millisecond, ...) would overflow for those dates.

DATEDIFF_BIG returns bigint and is available from SQL Server 2016. It still counts boundaries and still has a finite range, particularly with the smallest units. I also check the types used in subsequent calculations and in the tool receiving the result. Details appear in the DATEDIFF_BIG documentation.

MySQL: DATEDIFF takes two arguments

MySQL 8.4 does have DATEDIFF, but it takes two arguments in end, start order. It uses their date portions and ignores the time of day. I therefore do not add a unit as a first argument.

-- example: mysql-days
SELECT
    DATEDIFF('2024-06-01', '2024-01-01') AS days,
    DATEDIFF('2024-01-01', '2024-06-01') AS reversed_days,
    DATEDIFF('2026-02-02 00:01:00', '2026-02-01 23:59:00') AS calendar_days;

The expected results are 152, -152, and 1. Reversing the arguments changes the sign. In the final column, two minutes cross midnight, so the difference between date portions is one day. This is a useful check of whether a report concerns the calendar or the elapsed process time.

For other units, MySQL provides TIMESTAMPDIFF. Its argument order is unit, start, end. Both functions and their arguments appear in the MySQL 8.4 documentation.

-- example: mysql-units
SELECT
    TIMESTAMPDIFF(MINUTE, '2026-02-01 23:59:00', '2026-02-02 00:01:00') AS minutes,
    TIMESTAMPDIFF(DAY, '2026-02-01 23:59:00', '2026-02-02 00:01:00') AS full_days,
    TIMESTAMPDIFF(MONTH, '2026-01-31', '2026-02-28') AS full_months,
    TIMESTAMPDIFF(YEAR, '1990-06-15', '2026-06-14') AS completed_years;

The expected values are 2, 0, 0, and 35. Two minutes do not form a complete day. January 31 to February 28 does not form a complete month under the TIMESTAMPDIFF(MONTH, ...) rule. Age before the June birthday is 35. These are expectations based on documentation and manual checks of the examples, without MySQL execution during this revision.

PostgreSQL: date subtraction and AGE

When I need days between dates in PostgreSQL, I subtract date values. When I need a calendar description of the difference, I use AGE. A single query makes their different meanings and result types visible.

-- example: postgres-days
SELECT
    DATE '2024-06-01' - DATE '2024-01-01' AS days,
    AGE(TIMESTAMP '2024-06-01', TIMESTAMP '2024-01-01')::text AS calendar_age;

PostgreSQL 17.11 returned 152 and 5 mons. The second result contains no additional 31 days. January 1 to June 1 spans exactly five calendar months. Calling AGE does not automatically convert that period into a total number of days.

EXTRACT retrieves a particular field of a date or interval. Extracting DAY from 5 mons returns 0, rather than 152. Likewise, the MONTH field of an interval containing two years and three months is 3. A month count that includes years must account for both fields. Types and operations are described in the PostgreSQL 17 documentation.

This executed age example extracts the year field from AGE and uses fixed reference dates:

-- example: postgres-age
SELECT
    EXTRACT(YEAR FROM AGE(TIMESTAMP '2026-06-14', TIMESTAMP '1990-06-15'))::int AS before_birthday,
    EXTRACT(YEAR FROM AGE(TIMESTAMP '2026-06-15', TIMESTAMP '1990-06-15'))::int AS on_birthday;

It returned 35 and 36. For February 29, the AGE rule differs from the earlier SQL Server variation based on DATEADD. A check for a February 29, 2000 birth date gives 24 years on February 28, 2025, and 25 years on March 1, 2025. I preserve that difference as an explicit migration test case.

PostgreSQL: months and the end of February

The following executed example uses three pairs of dates. It retrieves years and months from the AGE interval and leaves out the remaining days. Every end is later than its start.

-- example: postgres-months
WITH spans(start_date, end_date) AS (
    VALUES (DATE '2026-01-15', DATE '2026-02-14'),
           (DATE '2026-01-15', DATE '2026-02-15'),
           (DATE '2026-01-31', DATE '2026-02-28')
), intervals AS (
    SELECT start_date, end_date,
           AGE(end_date::timestamp, start_date::timestamp) AS span
    FROM spans
)
SELECT start_date, end_date, span::text,
       (EXTRACT(YEAR FROM span) * 12 + EXTRACT(MONTH FROM span))::int AS full_months
FROM intervals
ORDER BY start_date, end_date;

This check shows why a single example beginning and ending on the first day of a month is weak evidence. Several different definitions produce the same result there. A partial month and an end-of-month case reveal the difference.

I do not use a calendar interval as a measurement of a program’s execution time. For duration, I need start and finish timestamps with an unambiguous zone or offset. Years and months in a calendar description have no fixed number of seconds.

Timestamps and daylight saving time in Warsaw

In PostgreSQL, timestamp without a time zone stores a clock reading without assigning it a specific UTC offset. timestamptz represents an instant. Here I compare local midnights before and after the spring clock change.

-- example: postgres-dst
SELECT
    (EXTRACT(EPOCH FROM (
        TIMESTAMPTZ '2026-03-30 00:00 Europe/Warsaw'
        - TIMESTAMPTZ '2026-03-29 00:00 Europe/Warsaw'
    )) / 3600)::int AS elapsed_hours,
    (EXTRACT(EPOCH FROM (
        TIMESTAMP '2026-03-30 00:00' - TIMESTAMP '2026-03-29 00:00'
    )) / 3600)::int AS wall_clock_hours;

The executed query returned 23 and 24. The first is the actual hour difference between the specified instants in Warsaw. The second comes from subtracting two timestamp values without a time zone. On a transition day, those calculations cannot be treated as equivalent.

I do not fix this by blindly appending UTC to every string. First I establish the source time zone. If a local hour is ambiguous in autumn, the string alone may not identify the instant. That missing information must be resolved during import.

The last 24 hours and the previous local calendar day also need separate definitions. Both filters can be useful, but their boundaries differ. I document the time zone alongside the reporting period so a later session-setting change does not silently alter the interpretation.

SQLite: numerical days and seconds

SQLite provides date functions but does not have a dedicated date type in the same sense as PostgreSQL. These examples use unambiguously formatted strings. julianday supports a difference in days, and unixepoch a difference in seconds.

-- example: sqlite-difference
SELECT
    julianday('2024-06-01') - julianday('2024-01-01') AS days,
    unixepoch('2026-06-01T10:05:30Z') - unixepoch('2026-06-01T10:00:00Z') AS seconds;

SQLite 3.53.4 returned 152 and 330. With timestamps including a time of day, a julianday difference may have a fractional part. It is floating point, so I do not describe it as an arbitrarily precise measurement. For these whole-second inputs, unixepoch gives an integer result.

For the Warsaw transition, I can supply explicit offsets. The following expression does not automatically resolve the named Europe/Warsaw zone; the correct offsets are part of the prepared input.

-- example: sqlite-offsets
SELECT (
    unixepoch('2026-03-30T00:00:00+02:00')
    - unixepoch('2026-03-29T00:00:00+01:00')
) / 3600.0 AS hours;

The result is 23. This test does not establish that SQLite will resolve every historical time-zone rule. The timediff function has a different purpose: it returns a textual description of a difference. For a numerical duration, I use the appropriate numerical subtraction. The available functions are described in the SQLite documentation.

The last 30 days: specify both boundaries

A condition such as DATEDIFF(day, order_date, now) <= 30 has another problem besides its unit: it admits future dates, because negative results are also less than 30. I therefore begin with an explicit interval and a decision about boundary inclusion.

This PostgreSQL example selects the last 720 hours before a fixed reference instant. The lower boundary is included and the upper boundary excluded. Synthetic input includes values just outside both boundaries and one in the future.

-- example: postgres-filter
WITH params AS (
    SELECT TIMESTAMPTZ '2026-10-21 12:00:00+00' AS as_of
), events(id, happened_at) AS (
    VALUES (1, TIMESTAMPTZ '2026-09-21 11:59:59+00'),
           (2, TIMESTAMPTZ '2026-09-21 12:00:00+00'),
           (3, TIMESTAMPTZ '2026-10-21 11:59:00+00'),
           (4, TIMESTAMPTZ '2026-10-21 12:00:00+00'),
           (5, TIMESTAMPTZ '2026-10-22 12:00:00+00')
)
SELECT id
FROM events CROSS JOIN params
WHERE happened_at >= as_of - INTERVAL '720 hours'
  AND happened_at < as_of
ORDER BY id;

The executed result contains only identifiers 2 and 3. Record 1 is too early, record 4 lies exactly on the excluded upper boundary, and record 5 is in the future. Intervals of the form [from, to) let adjacent windows meet without counting their shared boundary twice.

In a production query, I pass appropriately typed boundary parameters. Comparing the column directly with the boundaries may make index use easier, but I do not promise a particular speedup without an execution plan and data. I verify the selected records first, then performance.

NULL, reversed dates, and control cases

A missing task finish does not imply a zero duration. In the following table, I preserve NULL and calculate a separate status. An end before the start is labelled invalid for this particular process.

-- example: postgres-null
WITH tasks(id, started, finished) AS (
    VALUES (1, DATE '2026-02-01', DATE '2026-02-03'),
           (2, DATE '2026-02-01', NULL::date),
           (3, DATE '2026-02-03', DATE '2026-02-01')
)
SELECT id, finished - started AS days,
       CASE WHEN finished IS NULL THEN 'open'
            WHEN finished < started THEN 'invalid'
            ELSE 'complete' END AS status
FROM tasks
ORDER BY id;

The results are: identifier 1 has 2 days and status complete, identifier 2 has NULL and open, and identifier 3 has -2 and invalid. I do not replace the negative number with zero, because it exposes a problem worth investigating. In another task, a negative difference might simply express a valid comparison direction.

Before using your own variation, check identical dates, reversed arguments, month ends, February 29, the day before an anniversary, and the anniversary itself. For timestamps, include a time-zone or daylight saving transition and a missing endpoint. With repeated records, also check whether a later sum counts the same process more than once.

I deliver the engine name, column types, unit, and calendar rule with the report. That makes the result reproducible and comparable with another system. If your next step is to construct a date by adding an interval, also see DATEADD.