Return the number of whole days between two dates as end minus start.
DATEDIFF(end, start)## Overview Returns the number of whole days between two dates, computed as end minus start. The argument order is (end, start): the later date first, then the earlier date. The result is a signed INTEGER: positive when end is after start, negative when end is before start, zero for identical dates. Use DATEDIFF for age calculations, retention windows, SLA tracking, and any query that needs a whole-day count between two DATE values. For finer units (hours, minutes, months) use DATE_DIFF with an explicit unit. ## Behavior - Returns an INTEGER (number of days). - Returns NULL if either argument is NULL. - Argument order is (end, start). Reversing the order negates the result. - Counts whole days between the two calendar dates; no partial-day fractions. - Works across year and month boundaries naturally. - Accepts DATE inputs. TIMESTAMP inputs are cast to DATE first, discarding the time-of-day component. ## Compatibility - Day-count semantics equivalent to (end_date - start_date) in the SQL standard. The (end, start) argument order matches the widely adopted 'DATEDIFF(end, start)' convention.
| Name | Type | Description |
|---|---|---|
end | Specifies the end (later) date. The result is computed as end minus start. | |
start | Specifies the start (earlier) date. |
-- Days between two fixed dates
SELECT DATEDIFF(DATE '2025-03-15', DATE '2025-01-01') AS days;-- Negative result when end is earlier than start
SELECT DATEDIFF(DATE '2025-01-01', DATE '2025-03-15') AS days;-- Days elapsed since each order
SELECT order_id, DATEDIFF(CURRENT_DATE(), order_date) AS days_since_order
FROM commerce.sales.orders;-- Filter orders placed within the last 30 days
SELECT order_id, order_date
FROM commerce.sales.orders
WHERE DATEDIFF(CURRENT_DATE(), order_date) <= 30;-- Bucket support tickets by age
SELECT CASE
WHEN DATEDIFF(CURRENT_DATE(), opened_at::DATE) < 2 THEN 'fresh'
WHEN DATEDIFF(CURRENT_DATE(), opened_at::DATE) < 7 THEN 'week_old'
ELSE 'stale' END AS age_bucket,
COUNT(*) AS tickets
FROM support.tickets.open_tickets
GROUP BY age_bucket;-- Combine with GROUP BY to find weekly retention counts
SELECT DATEDIFF(last_seen, first_seen) / 7 AS weeks_retained, COUNT(*) AS users
FROM analytics.users.engagement
GROUP BY weeks_retained
ORDER BY weeks_retained;