Link Search Menu Expand Document

DATE_ADD

Calculates a new DATE or TIMESTAMP by adding or subtracting a specified number of time units from an indicated expression.

Syntax

DATE_ADD('<unit>', <interval>, <date_expr>)
Parameter Description
<unit> A unit of time. This can be any of the following: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR.
<interval> The number of times to increase the <date_expr> by the time unit specified by <unit>. This can be a negative number.
<date_expr> An expression that evaluates to a DATE or TIMESTAMP. value.

Example

The example below uses a table date_test with the columns and values below.

Category sale_date
a 2012-05-01
b 2021-08-30
c 1999-12-31

This example below adds 15 weeks to the sale_date column.

SELECT
	category,
	DATE_ADD('WEEK', 15, sale_date)
FROM
	date_test;

Returns:

+---+------------+
| a | 2012-08-14 |
| b | 2021-12-13 |
| c | 2000-04-14 |
+---+------------+

This example below subtracts 6 months from a given start date string. This string representation of a date first needs to be transformed to DATE type using the CAST function.

SELECT
    DATE_ADD('MONTH', -6, CAST ('2021-11-04' AS DATE));

Returns: 2021-05-04