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  
DAYNAME  
EXTRACT Alternative syntax for DATE_PART.
HOUR / MINUTE / SECOND Alternative syntax for DATE_PART.
LAST_DAY  
MONTHNAME  
YEAR / QUARTER / MONTH / WEEK / DAY / ... Alternative syntax for DATE_PART.
Addition/Subtraction ADD_MONTHS  
DATEADD  
DATEDIFF  
TIMEADD Alias for DATEADD.
TIMEDIFF Alias for DATEDIFF.
TIMESTAMPADD Alias for DATEADD.
TIMESTAMPDIFF Alias for DATEDIFF.
Truncation DATE_TRUNC  
TRUNC Alternative syntax for DATE_TRUNC.
Conversion TO_DATE  
TO_TIME  
TO_TIMESTAMP / TO_TIMESTAMP_*  
Time Zone CONVERT_TIMEZONE  

Supported Date and Time Parts

DATEADD, DATEDIFF, DATE_PART, DATE_TRUNC, and LAST_DAY, as well as their aliases/alternatives, accept a date or time part as an argument. The following table lists the parts (case-insensitive) that can be used with each function:

Date or Time Part
Abbreviations/Variations
DATEADD DATEDIFF DATE_PART DATE_TRUNC LAST_DAY Notes
year
y, yy, yyy, yyyy, yr, years
yrs
 
quarter
q, qtr, quarters, qtrs
 
month
mm, mon, months, mons
 
week
w, wk

ISO week (starting on Monday).

For DATE_PART (and EXTRACT), the returned week number corresponds to ISO 8601 weeks, where a week belongs to the year that contains the Thursday of that week:

  • For days in early January, value returned may be 52 or 53 (week belonging to the previous year).
  • For days in late December, value returned may be 1 (week belonging to the next year).
day
d, dd, days
   
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
         
weekofyear
woy, wy
        See notes for week.
dayofyear
yearday, doy, dy
         
dayofweek
weekday, dow, dw
       

Values returned are from 0 (Sunday) to 6 (Saturday).

Note that the week component returns weeks starting on Monday.

dayofweek_iso
weekday_iso, dow_iso, dw_iso
        Values returned are from 1 (Monday) to 7 (Sunday).
timezone_hour
tzh
         
timezone_minute
tzm
         

Calendar Weeks and Weekdays

  • In Snowflake, the calendar week starts on Monday. This practice follows the ISO-8601 data elements and interchange formats standard. The behavior influences functions such as DATEDIFF and DATE_TRUNC.
  • For functions such as DATE_PART and EXTRACT:
    • The week part returns the ISO week number, in accordance with the ISO 8601 date and time standard.
    • The dayofweek_iso part follows the ISO-8601 data elements and interchange formats standard. The function returns the day of the week as an integer value in the range 1-7, where 1 represents Monday.
    • For compatibility with some other systems, the dayofweek part follows the UNIX standard. The function returns the day of the week as an integer value in the range 0-6, where 0 represents Sunday.