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>', <value>, <expression>)

Parameters

Parameter Description Supported input types  
<datepart> A unit of time SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR  
<value> The number of times to increase the <datepart> by the time unit specified by <unit> Positive or negative number  
<expression> A date expression Any expression that evaluates to a DATE or TIMESTAMP value  

Return Types

DATE or TIMESTAMP

Example

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

player registeredon
steven70 2012-05-01
burchdenise 2021-08-30
stephanie86 1999-12-31

This example below adds 15 weeks to the registeredon column.

SELECT
	category,
	DATE_ADD('WEEK', 15, registeredon)
FROM
	player_registry;

Returns:

player registerdon
steven70 2012-08-14
burchdenise 2021-12-13
stephanie86 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