On this page

    Categories: Date/Time

    DATE_ADD

    Returns the sum of two expressions of time as another expression of time.

    Syntax

    DATE_ADD(date_expression STRING, days INTEGER) → DATE

    • date_expression: A string-formatted date in the format ‘YYYY-MM-DD’.
    • days: The number of days to be added to the specified date.

    Examples

    Adds two days to the specified date.

    SELECT DATE_ADD('2022-01-01', 2)
    -- 2022-01-03
    

    Adds negative two days to the specified date.

    SELECT DATE_ADD('2022-01-01', -2)
    -- 2021-12-30
    

    DATE_ADD(date_expression DATE, days INTEGER) → DATE

    • date_expression: The date, in the format ‘YYY-MM-DD’, to add days to. The value can be either a database column in DATE format, or literal value explicitly converted to DATE.
    • days: A 32-bit integer of the number of days to be added to the specified date.

    Examples

    Adds 30 days to the specified date.

    SELECT DATE_ADD(DATE '2022-01-01', 30)
    -- 2022-01-31
    

    Adds negative 30 days to the specified date.

    SELECT DATE_ADD(DATE '2022-01-01', -30)
    -- 2021-12-02
    

    DATE_ADD(date_expression STRING, time_interval INTERVAL) → TIMESTAMP

    • date_expression: A string-formatted date in the format ‘YYYY-MM-DD’.
    • time_interval: A CAST of a number to one of these intervals: DAY, MONTH, YEAR.

    Examples

    Adds two days to the specified date.

    SELECT DATE_ADD('2022-01-01', CAST(2 AS INTERVAL DAY))
    -- 2022-01-03 00:00:00
    

    Adds negative two days to the specified date.

    SELECT DATE_ADD('2022-01-01', CAST(-2 AS INTERVAL DAY))
    -- 2021-12-30 00:00:00
    

    DATE_ADD(date_expression DATE, time_interval INTERVAL) → TIMESTAMP

    • date_expression: The date, in the format ‘YYY-MM-DD’, to add the time interval to. The value can be either a database column in DATE format, or literal value explicitly converted to DATE.
    • time_interval: A CAST of a number to one of these intervals: DAY, MONTH, YEAR.

    Examples

    SELECT DATE_ADD(DATE '2022-01-01', CAST(30 AS INTERVAL DAY))
    -- 2022-01-31 00:00:00
    

    DATE_ADD(timestamp_expression STRING, time_interval INTERVAL) → TIMESTAMP

    • timestamp_expression: A string-formatted timestamp in the format ‘YYYY-MM-DD HH24:MI:SS’.
    • time_interval: A CAST of a number to one of these intervals: SECOND, MINUTE, HOUR, DAY, MONTH, YEAR.

    Examples

    Adds 30 days to the specified timestamp. Note that the time information is lost.

    SELECT DATE_ADD('2022-01-01 12:00:00', CAST(30 AS INTERVAL DAY))
    -- 2022-01-31 00:00:00
    

    Adds negative 30 days to the specified timestamp. Note that the time information is lost.

    SELECT DATE_ADD('2022-01-01 12:00:00', CAST(-30 AS INTERVAL DAY))
    -- 2021-12-02 00:00:00
    

    DATE_ADD(timestamp_expression TIMESTAMP, time_interval INTERVAL) → TIMESTAMP

    • timestamp_expression: The timestamp, in the format ‘YYYY-MM-DD HH:MM:SS’, to add days to. The value can be either a database column in TIMESTAMP format, or literal value explicitly converted to TIMESTAMP.
    • time_interval: A CAST of a number to one of these intervals: SECOND, MINUTE, HOUR, DAY, MONTH, YEAR.

    Examples

    Adds 30 days to the specified timestamp.

    SELECT DATE_ADD(TIMESTAMP '2022-01-01 12:00:00', CAST(30 AS INTERVAL DAY))
    -- 2022-01-31 12:00:00
    

    Adds negative 30 days to the specified timestamp.

    SELECT DATE_ADD(TIMESTAMP '2022-01-01 12:00:00', CAST(-30 AS INTERVAL DAY))
    -- 2021-12-02 12:00:00
    

    DATE_ADD(time_expression TIME, time_interval INTERVAL) → TIME

    • time_expression: The time, in the format ‘HH:MM:SS’, to add the time interval to. The value can be either a database column in TIMESTAMP format, or literal value explicitly converted to TIMESTAMP.
    • time_interval: A CAST of a number to one of these intervals: SECOND, MINUTE, HOUR.

    Examples

    Adds 30 minutes to the specified time.

    SELECT DATE_ADD(TIME '00:00:00', CAST(30 AS INTERVAL MINUTE))
    -- 00:30:00
    

    Adds negative 30 minutes to the specified time.

    SELECT DATE_ADD(TIME '00:00:00', CAST(-30 AS INTERVAL MINUTE))
    -- 84600