Date & Time Functions¶
This family of functions can be used to construct, convert, extract, or modify DATE/TIME/TIMESTAMP data.
List of Functions¶
Sub-category |
Function |
Notes |
---|---|---|
Construction |
||
Extraction |
Accepts all date and time parts (see next section for details). |
|
Alternative for DATE_PART. |
||
Alternative for DATE_PART. |
||
Accepts relevant date parts (see next section for details). |
||
Alternative for DATE_PART. |
||
Addition/Subtraction |
||
Accepts relevant date and time parts (see next section for details). |
||
Accepts relevant date and time parts (see next section for details). |
||
Alias for DATEADD. |
||
Alias for DATEDIFF. |
||
Alias for DATEADD. |
||
Alias for DATEDIFF. |
||
Truncation |
Accepts relevant date and time parts (see next section for details). |
|
Allows a time to be “rounded” to the start of an evenly-spaced interval. |
||
Alternative for DATE_TRUNC. |
||
Conversion |
||
Time Zone |
||
Alerts |
||
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 |
---|---|---|---|---|---|---|
|
|
✔ |
✔ |
✔ |
✔ |
✔ |
|
|
✔ |
✔ |
✔ |
✔ |
✔ |
|
|
✔ |
✔ |
✔ |
✔ |
|
|
|
✔ |
||||
|
|
✔ |
||||
|
|
✔ |
||||
|
|
✔ |
✔ |
✔ |
✔ |
✔ |
|
|
✔ |
||||
|
|
✔ |
✔ |
✔ |
✔ |
✔ |
|
✔ |
|||||
|
✔ |
[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 |
---|---|---|---|---|---|---|
|
|
✔ |
✔ |
✔ |
✔ |
|
|
|
✔ |
✔ |
✔ |
✔ |
|
|
|
✔ |
✔ |
✔ |
✔ |
|
|
|
✔ |
✔ |
✔ |
||
|
|
✔ |
✔ |
✔ |
||
|
|
✔ |
✔ |
✔ |
✔ |
|
|
|
✔ |
||||
|
|
✔ |
||||
|
|
✔ |
||||
|
|
✔ |
||||
|
|
✔ |
||||
|
|
✔ |
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 |
Param set to |
---|---|---|
Returns |
Returns |
|
DATE_TRUNC (with a |
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 |
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 |
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
to7
: 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 | +------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------+
With WEEK_START set to
0
, the DOW for Sunday is0
.With WEEK_START set to
1
, the DOW for Sunday is7
.
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 | +------------+-----+-----+-----------+------+-----------+
With WEEK_OF_YEAR_POLICY set to
1
and WEEK_START set to1
(Monday):
WOY for
2017-01-01
is1
.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.
With WEEK_OF_YEAR_POLICY set to
1
and WEEK_START set to3
(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.