DATE_ADD

Add (or subtract) a signed number of days to a date.

Category: datetimeReturns: DATEDialect: Standard

Syntax

DATE_ADD(date, days)

Description

## Overview Adds a signed integer number of days to a DATE and returns the resulting DATE. This is the most common form of day-level date arithmetic: rolling forward to a deadline, rolling back to a lookback window start, or computing date offsets driven by a column-valued day count. For month- or year-level arithmetic use ADD_MONTHS; for sub-day arithmetic on TIMESTAMP values use INTERVAL addition. ## Behavior - Returns a DATE. - Returns NULL if either argument is NULL. - Negative values subtract days; DATE_ADD(d, -n) is equivalent to DATE_SUB(d, n). - DATE_ADD(d, 0) returns d unchanged. - Day counts advance through month and year boundaries naturally (no end-of-month clamping). - The day count is INT; very large values that overflow the representable DATE range raise an error. - Time-of-day is not involved: inputs and outputs are DATE values. ## Compatibility - Day-based date arithmetic matching the standard 'DATE + INTEGER days' semantics. Equivalent to the expression date + N when the SQL engine supports INTEGER-days addition directly on DATE.

Parameters

Name	Type	Description
`date`		Specifies the starting date to which days are added.
`days`		Specifies the number of days to add. Negative values subtract days. Fractional day counts are not supported; use INTERVAL arithmetic for sub-day offsets.

Examples

-- Add ten days to a fixed date
SELECT DATE_ADD(DATE '2025-03-01', 10) AS result;

-- Subtract five days using a negative count
SELECT DATE_ADD(DATE '2025-03-01', -5) AS result;

-- 30 days from today
SELECT DATE_ADD(CURRENT_DATE(), 30) AS deadline;

-- Compute expected arrival per shipment row
SELECT order_id, DATE_ADD(ship_date, transit_days) AS eta
FROM commerce.logistics.shipments;

-- Month-end advance: April 30 + 1 day = May 1
SELECT DATE_ADD(DATE '2025-04-30', 1) AS result;

-- Bucket orders into week-start cohorts
SELECT DATE_ADD(order_date, -DAYOFWEEK(order_date) + 1) AS week_start,
       COUNT(*) AS orders
FROM commerce.sales.orders
GROUP BY week_start
ORDER BY week_start;

Pitfalls

DATE_ADD works in whole days. Adding a fractional day (for example 1.5) is rejected; use INTERVAL '36 hours' with a TIMESTAMP base if you need sub-day offsets.
Adding days does not respect end-of-month semantics. DATE_ADD('2025-01-31', 1) returns 2025-02-01, not February 28. Use ADD_MONTHS when you want calendar-month arithmetic.
Adding a very large day count can exceed the supported DATE range and raise an error; validate large offsets from column data before applying.