Snowflake
Modify Date/Time

Snowflake DATEADD: Modify a Date or Time

The DATEADD function in Snowflake is like a time machine for your data. It allows you to add or even subtract time from a specific date or timestamp, making it useful for adjusting dates, or timestamp columns.

What it does

DATEADD lets you manipulate dates by adding a specific amount of time—anything from nanoseconds to years—to a given date, time, or timestamp.

Syntax

Here’s how you write it:


_10
DATEADD(`<date_or_time_unit_type>`, `<units>`, `<date_or_time_expr>`)

where:

  • <date_or_time_unit_type>: The type of time unit you want to add (e.g., year, month, quarter, week, day, hour, minute, second, millisecond, microsecond, or nanosecond).
  • <units>: How many of these units you want to add (can be positive to add time or negative to subtract time).
  • <date_or_time_expr>: The starting date or time to which you'll add or subtract time.

Common use cases

  • Calculating expiration dates: For products or subscriptions that expire after a certain period

_10
SELECT
_10
DATEADD ('year', 1, purchase_date) AS expiration_date
_10
FROM
_10
subscriptions;

  • Analyzing fiscal quarters: To adjust dates to the start of the next fiscal quarter

_10
SELECT
_10
DATEADD ('quarter', 1, current_date) AS next_quarter_start
_10
FROM
_10
fiscal_calendar;

Examples

Add days to a date

Input

Table: guide_events

event_nameevent_date
Arthur's Galactic Travel Start1979-10-12
Marvin's Birthday1981-01-05

Snowflake SQL Query


_10
SELECT
_10
event_name,
_10
event_date,
_10
DATEADD ('day', 42, event_date) AS event_date_plus_42
_10
FROM
_10
guide_events;

Output

event_nameevent_dateevent_date_plus_42
Arthur's Galactic Travel Start1979-10-121979-11-23
Marvin's Birthday1981-01-051981-02-16

Add hours to a timestamp

Input

Table: guide_events

event_nameevent_timestamp
Arthur's Galactic Travel Start1979-10-12 00:00:00.000
Marvin's Birthday1981-01-05 03:00:00.000

Snowflake SQL Query


_10
SELECT
_10
event_name,
_10
event_timestamp,
_10
DATEADD ('hour', 21, event_timestamp) AS event_timestamp_plus_21
_10
FROM
_10
guide_events;

Output

event_nameevent_timestampevent_timestamp_plus_21
Arthur's Galactic Travel Start1979-10-12 00:00:00.0001979-10-12 21:00:00.000
Marvin's Birthday1981-01-05 03:00:00.0001981-01-06 00:00:00.000
Y42 SQL completion user interface

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Start for free