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  
YEAR / MONTH / DAY / QUARTER / WEEK … 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).
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  
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 date and time format standard, 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 next sections (in this topic).

Controlling the 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 Name WEEK_START = 0 (Default / Legacy Behavior) WEEK_START = 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.

Examples

Note

These examples query the same set of date functions, but with different values set for WEEK_START to illustrate how this parameter influences the results of the functions.

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 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.

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 - 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 WEEK_OF_YEAR_POLICY and WEEK_START to illustrate how these parameters influence the results of the functions.

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.