Date & Time Functions

This family of functions can be used to construct, convert, extract, or modify DATE/TIME/TIMESTAMP data.

In this Topic:

List of Functions

Sub-category

Function

Notes

Construction

DATE_FROM_PARTS

TIME_FROM_PARTS

TIMESTAMP_FROM_PARTS

Extraction

DATE_PART

Accepts all date and time parts (see next section for details).

DAYNAME

EXTRACT

Alternative for DATE_PART.

HOUR / MINUTE / SECOND

Alternative for DATE_PART.

LAST_DAY

Accepts relevant date parts (see next section for details).

MONTHNAME

NEXT_DAY

PREVIOUS_DAY

YEAR* / DAY* / WEEK* / MONTH / QUARTER

Alternative for DATE_PART.

Addition/Subtraction

ADD_MONTHS

DATEADD

Accepts relevant date and time parts (see next section for details).

DATEDIFF

Accepts relevant date and time parts (see next section for details).

TIMEADD

Alias for DATEADD.

TIMEDIFF

Alias for DATEDIFF.

TIMESTAMPADD

Alias for DATEADD.

TIMESTAMPDIFF

Alias for DATEDIFF.

Truncation

DATE_TRUNC

Accepts relevant date and time parts (see next section for details).

TIME_SLICE

Allows a time to be “rounded” to the start of an evenly-spaced interval.

TRUNC

Alternative for DATE_TRUNC.

Conversion

TO_DATE

TO_TIME

TO_TIMESTAMP / TO_TIMESTAMP_*

Time Zone

CONVERT_TIMEZONE

Supported Date and Time Parts

Certain functions (as well as their appropriate aliases and alternatives) accept a date or time part as an argument. The following two tables list the parts (case-insensitive) that can be used with these functions.

Date Parts

Abbreviations / Variations

DATEADD

DATEDIFF

DATE_PART

DATE_TRUNC

LAST_DAY

year

y , yy , yyy , yyyy , yr , years , yrs

month

mm , mon , mons , months

day

d , dd , days, dayofmonth

dayofweek [1]

weekday , dow , dw

dayofweekiso [2]

weekday_iso , dow_iso , dw_iso

dayofyear

yearday , doy , dy

week [1]

w , wk , weekofyear , woy , wy

weekiso [2]

week_iso , weekofyeariso , weekofyear_iso

quarter

q , qtr , qtrs , quarters

yearofweek [1]

yearofweekiso [2]

yearofweek_iso

[1] For usage details, see the next section, which describes how Snowflake handles calendar weeks and weekdays.

[2] Not controlled by the WEEK_START and WEEK_OF_YEAR_POLICY session parameters, as described in the next section.

Time Parts

Abbreviations / Variations

DATEADD

DATEDIFF

DATE_PART

DATE_TRUNC

LAST_DAY

hour

h , hh , hr , hours , hrs

minute

m , mi , min , minutes , mins

second

s , sec , seconds , secs

millisecond

ms , msec , milliseconds

microsecond

us , usec , microseconds

nanosecond

ns , nsec , nanosec , nsecond , nanoseconds , nanosecs , nseconds

epoch_second

epoch , epoch_seconds

epoch_millisecond

epoch_milliseconds

epoch_microsecond

epoch_microseconds

epoch_nanosecond

epoch_nanoseconds

timezone_hour

tzh

timezone_minute

tzm

Calendar Weeks and Weekdays

The behavior of week-related functions in Snowflake is controlled by the WEEK_START and WEEK_OF_YEAR_POLICY session parameters. An important aspect of understanding how these parameters interact is the concept of ISO weeks.

ISO Weeks

As defined in the ISO 8601 standard (for dates and time formats), ISO weeks always start on Monday and “belong” to the year that contains the Thursday of that week. This means that a day in one year might belong to a week in a different year:

  • For days in early January, the WOY (week of the year) value can be 52 or 53 (i.e. the day belongs to the last week in the previous year).

  • For days in late December, the WOY value can be 1 (i.e. the day belongs to the first week in the next year).

Snowflake provides a special set of week-related date functions (and equivalent data parts) whose behavior is consistent with the ISO week semantics: DAYOFWEEKISO , WEEKISO , YEAROFWEEKISO.

These functions (and date parts) disregard the session parameters (i.e. they always follow the ISO semantics).

For details about how all the other week-related date functions are handled, see the following sections (in this topic).

First Day of the Week

Most week-related functions are controlled only by the WEEK_START session parameter. Note that the function results differ depending on how the parameter is set:

Function

Param set to 0 (default / legacy behavior)

Param set to 1 - 7 (Monday - Sunday)

DAY and DAYOFWEEK

Returns 0 (Sunday) to 6 (Saturday).

Returns 1 (defined first day of the week) to 7 (last day of the week relative to the defined first day).

DATE_TRUNC (with a WEEK part)

Truncates the input week to start on Monday.

Truncates the input week to start on the defined first day of the week.

LAST_DAY (with a WEEK part)

Returns the Sunday of the input week.

Returns the last day of the input week relative to the defined first day of the week.

DATEDIFF (with a WEEK part)

Calculated using weeks starting on Monday.

Calculated using weeks starting on the defined first day of the week.

Tip

The default value for the parameter is 0, which preserves the legacy Snowflake behavior (ISO-like semantics); however, we recommend changing this value to explicitly control the resulting behavior of the functions. Based on feedback we’ve received, the most common scenario is to set the parameter to 1.

First/Last Weeks of the Year

The WEEK_OF_YEAR_POLICY session parameter controls how the WEEK and YEAROFWEEK functions behave. The parameter can have two values:

  • 0: The affected week-related functions use semantics similar to the ISO semantics, in which a week belongs to a given year if at least 4 days of that week are in that year. This means that all the weeks have 7 days, but the first days of January and the last days of December might belong to a week in a different year. For this reason, both the YEAROFWEEK and YEAROFWEEKISO functions can provide the year that the week belongs to.

  • 1: January 1 always starts the first week of the year and December 31 is always in the last week of the year. This means that the first week and last week in the year may have fewer than 7 days.

Note that this behavior is also influenced by the start day of the week, as controlled by the value set for the WEEK_START session parameter:

  • 0 , 1: The behavior is equivalent to the ISO week semantics, with the week starting on Monday.

  • 2 to 7: The “4 days” logic is preserved, but the first day of the week is different.

Tip

The default value for both parameters is 0, which preserves the legacy Snowflake behavior (ISO-like semantics); however, we recommend changing these values to explicitly control the resulting behavior of the functions. Based on feedback we’ve received, the most common scenario is to set both parameters to 1.

Examples

Note

These examples query the same set of date functions, but with different values set for the WEEK_OF_YEAR_POLICY and WEEK_START session parameters to illustrate how they influence the results of the functions.

Controlling the First Day of the Week

Setting WEEK_START to 0 (legacy behavior) or 1 (Monday) does not have a significant effect, as illustrated in the following two examples:

ALTER SESSION SET WEEK_START = 0;

SELECT d "Date", DAYNAME(d) "Day",
       DAYOFWEEK(d) "DOW",
       DATE_TRUNC('week', d) "Trunc Date",
       DAYNAME("Trunc Date") "Trunc Day",
       LAST_DAY(d, 'week') "Last DOW Date",
       DAYNAME("Last DOW Date") "Last DOW Day",
       DATEDIFF('week', '2017-01-01', d) "Weeks Diff from 2017-01-01 to Date"
FROM week_examples;

+------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------+
| Date       | Day | DOW | Trunc Date | Trunc Day | Last DOW Date | Last DOW Day | Weeks Diff from 2017-01-01 to Date |
|------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------|
| 2016-12-30 | Fri |   5 | 2016-12-26 | Mon       | 2017-01-01    | Sun          |                                  0 |
| 2016-12-31 | Sat |   6 | 2016-12-26 | Mon       | 2017-01-01    | Sun          |                                  0 |
| 2017-01-01 | Sun |   0 | 2016-12-26 | Mon       | 2017-01-01    | Sun          |                                  0 |
| 2017-01-02 | Mon |   1 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-01-03 | Tue |   2 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-01-04 | Wed |   3 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-01-05 | Thu |   4 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-12-30 | Sat |   6 | 2017-12-25 | Mon       | 2017-12-31    | Sun          |                                 52 |
| 2017-12-31 | Sun |   0 | 2017-12-25 | Mon       | 2017-12-31    | Sun          |                                 52 |
+------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------+

ALTER SESSION SET WEEK_START = 1;

SELECT d "Date", DAYNAME(d) "Day",
       DAYOFWEEK(d) "DOW",
       DATE_TRUNC('week', d) "Trunc Date",
       DAYNAME("Trunc Date") "Trunc Day",
       LAST_DAY(d, 'week') "Last DOW Date",
       DAYNAME("Last DOW Date") "Last DOW Day",
       DATEDIFF('week', '2017-01-01', d) "Weeks Diff from 2017-01-01 to Date"
FROM week_examples;

+------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------+
| Date       | Day | DOW | Trunc Date | Trunc Day | Last DOW Date | Last DOW Day | Weeks Diff from 2017-01-01 to Date |
|------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------|
| 2016-12-30 | Fri |   5 | 2016-12-26 | Mon       | 2017-01-01    | Sun          |                                  0 |
| 2016-12-31 | Sat |   6 | 2016-12-26 | Mon       | 2017-01-01    | Sun          |                                  0 |
| 2017-01-01 | Sun |   7 | 2016-12-26 | Mon       | 2017-01-01    | Sun          |                                  0 |
| 2017-01-02 | Mon |   1 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-01-03 | Tue |   2 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-01-04 | Wed |   3 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-01-05 | Thu |   4 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-12-30 | Sat |   6 | 2017-12-25 | Mon       | 2017-12-31    | Sun          |                                 52 |
| 2017-12-31 | Sun |   7 | 2017-12-25 | Mon       | 2017-12-31    | Sun          |                                 52 |
+------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------+
  1. With WEEK_START set to 0, the DOW for Sunday is 0.

  2. With WEEK_START set to 1, the DOW for Sunday is 7.

The results differ more significantly if WEEK_START is set to any day other than Monday. For example, setting the parameter to 3 (Wednesday) changes the results of all the week-related functions (columns 3 through 8):

ALTER SESSION SET WEEK_START = 3;

SELECT d "Date", DAYNAME(d) "Day",
       DAYOFWEEK(d) "DOW",
       DATE_TRUNC('week', d) "Trunc Date",
       DAYNAME("Trunc Date") "Trunc Day",
       LAST_DAY(d, 'week') "Last DOW Date",
       DAYNAME("Last DOW Date") "Last DOW Day",
       DATEDIFF('week', '2017-01-01', d) "Weeks Diff from 2017-01-01 to Date"
FROM week_examples;

+------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------+
| Date       | Day | DOW | Trunc Date | Trunc Day | Last DOW Date | Last DOW Day | Weeks Diff from 2017-01-01 to Date |
|------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------|
| 2016-12-30 | Fri |   3 | 2016-12-28 | Wed       | 2017-01-03    | Tue          |                                  0 |
| 2016-12-31 | Sat |   4 | 2016-12-28 | Wed       | 2017-01-03    | Tue          |                                  0 |
| 2017-01-01 | Sun |   5 | 2016-12-28 | Wed       | 2017-01-03    | Tue          |                                  0 |
| 2017-01-02 | Mon |   6 | 2016-12-28 | Wed       | 2017-01-03    | Tue          |                                  0 |
| 2017-01-03 | Tue |   7 | 2016-12-28 | Wed       | 2017-01-03    | Tue          |                                  0 |
| 2017-01-04 | Wed |   1 | 2017-01-04 | Wed       | 2017-01-10    | Tue          |                                  1 |
| 2017-01-05 | Thu |   2 | 2017-01-04 | Wed       | 2017-01-10    | Tue          |                                  1 |
| 2017-12-30 | Sat |   4 | 2017-12-27 | Wed       | 2018-01-02    | Tue          |                                 52 |
| 2017-12-31 | Sun |   5 | 2017-12-27 | Wed       | 2018-01-02    | Tue          |                                 52 |
+------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------+

Controlling the Year and Days for the First/Last Weeks of the Year

The following example sets both parameters to 0 to follow ISO-like semantics (i.e. week starts on Monday and all weeks have 7 days):

ALTER_SESSION SET WEEK_OF_YEAR_POLICY=0, WEEK_START=0;

SELECT d "Date", DAYNAME(d) "Day",
       WEEK(d) "WOY",
       WEEKISO(d) "WOY (ISO)",
       YEAROFWEEK(d) "YOW",
       YEAROFWEEKISO(d) "YOW (ISO)"
FROM week_examples;

+------------+-----+-----+-----------+------+-----------+
| Date       | Day | WOY | WOY (ISO) |  YOW | YOW (ISO) |
|------------+-----+-----+-----------+------+-----------|
| 2016-12-30 | Fri |  52 |        52 | 2016 |      2016 |
| 2016-12-31 | Sat |  52 |        52 | 2016 |      2016 |
| 2017-01-01 | Sun |  52 |        52 | 2016 |      2016 |
| 2017-01-02 | Mon |   1 |         1 | 2017 |      2017 |
| 2017-01-03 | Tue |   1 |         1 | 2017 |      2017 |
| 2017-01-04 | Wed |   1 |         1 | 2017 |      2017 |
| 2017-01-05 | Thu |   1 |         1 | 2017 |      2017 |
| 2017-12-30 | Sat |  52 |        52 | 2017 |      2017 |
| 2017-12-31 | Sun |  52 |        52 | 2017 |      2017 |
+------------+-----+-----+-----------+------+-----------+

The next example illustrates the effect of keeping WEEK_OF_YEAR_POLICY set to 0, but changing WEEK_START to 3 (Wednesday):

ALTER_SESSION SET WEEK_OF_YEAR_POLICY=0, WEEK_START=3;

SELECT d "Date", DAYNAME(d) "Day",
       WEEK(d) "WOY",
       WEEKISO(d) "WOY (ISO)",
       YEAROFWEEK(d) "YOW",
       YEAROFWEEKISO(d) "YOW (ISO)"
FROM week_examples;

+------------+-----+-----+-----------+------+-----------+
| Date       | Day | WOY | WOY (ISO) |  YOW | YOW (ISO) |
|------------+-----+-----+-----------+------+-----------|
| 2016-12-30 | Fri |  53 |        52 | 2016 |      2016 |
| 2016-12-31 | Sat |  53 |        52 | 2016 |      2016 |
| 2017-01-01 | Sun |  53 |        52 | 2016 |      2016 |
| 2017-01-02 | Mon |  53 |         1 | 2016 |      2017 |
| 2017-01-03 | Tue |  53 |         1 | 2016 |      2017 |
| 2017-01-04 | Wed |   1 |         1 | 2017 |      2017 |
| 2017-01-05 | Thu |   1 |         1 | 2017 |      2017 |
| 2017-12-30 | Sat |  52 |        52 | 2017 |      2017 |
| 2017-12-31 | Sun |  52 |        52 | 2017 |      2017 |
+------------+-----+-----+-----------+------+-----------+
  • 2016 now has 53 weeks (instead of 52).

  • WOY for Jan 1st, 2017 moves to week 53 (from 52).

  • WOY for Jan 2nd and 3rd, 2017 moves to week 53 (from 1).

  • YOW for Jan 2nd and 3rd, 2017 moves to 2016 (from 2017).

  • WOY (ISO) and YOW (ISO) are not affected by the parameter change.

The last two examples set WEEK_OF_YEAR_POLICY to 1 and set WEEK_START first to 1 (Monday) and then 3 (Wednesday):

ALTER_SESSION SET WEEK_OF_YEAR_POLICY=1, WEEK_START=1;

SELECT d "Date", DAYNAME(d) "Day",
       WEEK(d) "WOY",
       WEEKISO(d) "WOY (ISO)",
       YEAROFWEEK(d) "YOW",
       YEAROFWEEKISO(d) "YOW (ISO)"
FROM week_examples;

+------------+-----+-----+-----------+------+-----------+
| Date       | Day | WOY | WOY (ISO) |  YOW | YOW (ISO) |
|------------+-----+-----+-----------+------+-----------|
| 2016-12-30 | Fri |  53 |        52 | 2016 |      2016 |
| 2016-12-31 | Sat |  53 |        52 | 2016 |      2016 |
| 2017-01-01 | Sun |   1 |        52 | 2017 |      2016 |
| 2017-01-02 | Mon |   2 |         1 | 2017 |      2017 |
| 2017-01-03 | Tue |   2 |         1 | 2017 |      2017 |
| 2017-01-04 | Wed |   2 |         1 | 2017 |      2017 |
| 2017-01-05 | Thu |   2 |         1 | 2017 |      2017 |
| 2017-12-30 | Sat |  53 |        52 | 2017 |      2017 |
| 2017-12-31 | Sun |  53 |        52 | 2017 |      2017 |
+------------+-----+-----+-----------+------+-----------+

alter session set week_of_year_policy=1, week_start=3;

SELECT d "Date", DAYNAME(d) "Day",
       WEEK(d) "WOY",
       WEEKISO(d) "WOY (ISO)",
       YEAROFWEEK(d) "YOW",
       YEAROFWEEKISO(d) "YOW (ISO)"
FROM week_examples;

+------------+-----+-----+-----------+------+-----------+
| Date       | Day | WOY | WOY (ISO) |  YOW | YOW (ISO) |
|------------+-----+-----+-----------+------+-----------|
| 2016-12-30 | Fri |  53 |        52 | 2016 |      2016 |
| 2016-12-31 | Sat |  53 |        52 | 2016 |      2016 |
| 2017-01-01 | Sun |   1 |        52 | 2017 |      2016 |
| 2017-01-02 | Mon |   1 |         1 | 2017 |      2017 |
| 2017-01-03 | Tue |   1 |         1 | 2017 |      2017 |
| 2017-01-04 | Wed |   2 |         1 | 2017 |      2017 |
| 2017-01-05 | Thu |   2 |         1 | 2017 |      2017 |
| 2017-12-30 | Sat |  53 |        52 | 2017 |      2017 |
| 2017-12-31 | Sun |  53 |        52 | 2017 |      2017 |
+------------+-----+-----+-----------+------+-----------+
  1. With WEEK_OF_YEAR_POLICY set to 1 and WEEK_START set to 1 (Monday):

    • WOY for 2017-01-01 is 1.

    • Week 1 consists of 1 day.

    • Week 2 starts on Mon.

    Note that this is the most common usage scenario, based on feedback we’ve received.

  2. With WEEK_OF_YEAR_POLICY set to 1 and WEEK_START set to 3 (Wednesday):

    • WOY for 2017-01-01 is still 1.

    • Week 1 consists of 3 days.

    • Week 2 starts on Wed.

In both examples, WOY (ISO) and YOW (ISO) are not affected by the parameter change.