Subtract a signed number of days from a date.
DATE_SUB(date, days)## Overview Subtracts a signed integer number of days from a DATE and returns the resulting DATE. Equivalent to DATE_ADD(date, -days); both forms exist for readability. Use DATE_SUB in rolling-window filters, lookback calculations, retention cohorts, and any context where 'N days ago' expresses intent more clearly than a negative addition. ## Behavior - Returns a DATE. - Returns NULL if either argument is NULL. - Negative counts effectively add days; DATE_SUB(d, -n) equals DATE_ADD(d, n). - DATE_SUB(d, 0) returns d unchanged. - Day counts subtract through month and year boundaries naturally. - Very large day counts that drive the result below the supported DATE range raise an error. - Time-of-day is not involved. ## Compatibility - Day-based subtraction matching the standard 'DATE - INTEGER days' semantics.
| Name | Type | Description |
|---|---|---|
date | Specifies the starting date from which days are subtracted. | |
days | Specifies the number of days to subtract. Negative values effectively add days. Fractional counts are not supported. |
-- Subtract ten days from a fixed date
SELECT DATE_SUB(DATE '2025-03-15', 10) AS result;-- A date 30 days in the past
SELECT DATE_SUB(CURRENT_DATE(), 30) AS thirty_days_ago;-- Negative count effectively adds days
SELECT DATE_SUB(DATE '2025-01-01', -5) AS result;-- Filter the last 90 days of orders
SELECT order_id, order_date, total_amount
FROM commerce.sales.orders
WHERE order_date >= DATE_SUB(CURRENT_DATE(), 90);-- Compute a rolling seven-day retention anchor
SELECT user_id,
DATE_SUB(last_active_date, 7) AS retention_cutoff
FROM analytics.users.engagement;-- Group event counts by day offset from today
SELECT DATEDIFF(CURRENT_DATE(), event_date) AS days_ago,
COUNT(*) AS events
FROM analytics.events.user_activity
WHERE event_date >= DATE_SUB(CURRENT_DATE(), 30)
GROUP BY days_ago
ORDER BY days_ago;