Skip to main content

Date/Time

Function NameDescription
CONVERT_TIMEZONEConvert timestamp to the specified timezone
CURRENT_DATEReturns the current date of the system.
CURRENT_DATE_UTCReturns the current date of the system based on the UTC timezone.
CURRENT_TIMEReturns the current time for the system.
CURRENT_TIMESTAMPReturns the current timestamp for the system in UTC time only.
DATEDIFFCompares two dates or timestamps and returns the difference in days.
DATETYPEConstructs DATE using the values provided for year, month, and day parameters.
DATE_ADDReturns the sum of two expressions of time as another expression of time.
DATE_DIFFReturns the difference between two expressions of time as another expression of time.
DATE_PARTReturn subfields such as year or hour from date or timestamp values
DATE_SUBReturns the difference of two expressions of time as another expression of time.
DATE_TRUNCTruncates the date or timestamp to the indicated precision.
DAYReturns the day of month of the date or timestamp.
DAYOFMONTHReturns the day of month of the date or timestamp.
DAYOFWEEKReturns the day of the week (from 1 to 7) of the date or timestamp.
DAYOFYEARReturns the day of the year (from 1 to 366) of the date or timestamp.
EXTRACTExtracts the specified date or time part from the date or timestamp.
HOURExtracts the hour number (from 0 to 23) for a given time or timestamp.
LAST_DAYReturns the last day of the month for the specified date or timestamp.
MINUTEExtracts the minute number (from 0 to 59) for a given time or timestamp.
MONTHExtracts the month number (from 1 to 12) for a given date or timestamp.
MONTHS_BETWEENReturns the number of months between two date or timestamp values.
NEXT_DAYReturns the date or timestamp of the first specified day of week that occurs after the input date.
OVERLAPSReturns whether two intervals overlap.
QUARTERExtracts the quarter number (from 1 to 4) for a given date or timestamp.
SECONDExtracts the second number (from 0 to 59) for a given date or timestamp.
TIMESTAMPADDAdd (or subtract) an interval of time from a date/timestamp value or column.
TIMESTAMPDIFFReturn the amount of time between two date or timestamp values
TIMESTAMPTYPEConstructs a timestamp using the values provided for year, month, day, hour, minute, second, and millisecond parameters.
TO_DATEConverts the input expressions to the corresponding date.
TO_TIMEConverts the input expressions to the corresponding time.
TO_TIMESTAMPConverts the input expressions to the corresponding timestamp.
UNIX_TIMESTAMPReturns the Unix epoch time representation of an ISO 8601 timestamp.
WEEKExtracts the week number (from 0 to 53) for a given date or timestamp.
WEEKOFYEARReturns the week of year of the date or timestamp.
YEARExtracts the year for a given date or timestamp.

Date/Time Formatting

Format ElementDescriptionExample
AD/BCEra indicatorAD, BC
AMPMMeridian indicatorAM, PM
CCCentury indicator (0-99)19
WWWeek of year (0-52)4, 43
DDay of week (1-7)6
DYAbbreviated day name of weekTue, Fri
DAYFull day name of weekTuesday, Friday
YYYYFour digits of year1996
YYLast two digits of year96
DDDDay of year (1-366)5, 245
MMMonth (1-12)8
MONAbbreviated month nameMar, Oct
MONTHFull month nameMarch, October
DDDay of month (1-31)24
HH/HH12Hour of day (1-12)4
HH24Hour of day (0-23)21
MIMinutes (0-59)22
SSSeconds (0-59)54
FFFMilliseconds121
TZDTimezone abbreviationUTC, PST
TZOTimezone offset+02:00, -0800

Only the following characters are allowed in a format pattern: - / , . ; :. If you need to let a value pass through to the output unmodified you can surround it with " (for example "T").

If you convert a date to text, numeric values are zero padded for you. For example, MM returns 04 for April.