Datetime Functions

CURRENT_DATETIME
CURRENT_DATETIME([time_zone])
Description
Return the current date and time. 
Input
timezone(optional, STRING): The default timezone is UTC. You can optionally specify a time zone. 
Return Data Type
DATETIME
Example
CURRENT_DATETIME()
Result: 2021-04-26T18:24:38.702355
CURRENT_DATETIME("America/New_York")
Result: 2021-04-26T14:24:38.702355
DATETIME
DATETIME(datetime_expression)
Description
Convert a string, timestamp, or date expression to a datetime variable in a "yyyy-mm-dd hh-mm-ss" format. For the timestamp inputs, you can optionally specify a time zone.
Input
a STRING, TIMESTAMP object or column
Return Data Type
DATETIME
Example
DATETIME("2020-12-01 14:20:32")
Result: 2020-12-01T14:20:32
DATETIME(TIMESTAMP("2020-12-01 14:20:32+07"), "America/Los_Angeles")
Result: 2020-11-30T23:20:32
EXTRACT
EXTRACT(date_part FROM datetime_expression)
Description
Return a part of the datetime object. 
Input
date_part: can be one of the following: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAYOFWEEK, DAY, DAYOFYEAR, WEEK(begins on Sunday), ISOWEEK(begin on Monday), MONTH, QUARTER, YEAR, ISOYEAR, DATE, or TIME (See more details)
datetime: a DATETIME object or column
Return Data Type
INTEGER, except in the following cases:
If date_part is DATE, returns a DATE object.
If date_part is TIME, returns a TIME object.
Example
EXTRACT(DAY FROM DATETIME("2020-12-01 14:20:32"))
Result: 1
EXTRACT(DAYOFWEEK FROM DATETIME("2020-12-01 14:20:32"))
Result: 3 (1=Sunday, 2=Monday, 3=Tuesday....) 
DATETIME_ADD
DATETIME_ADD(datetime, INTERVAL integer datetime_part)
Description
Add a specified time interval to the given datetime. 
Input
date_part: can be one of the following: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAYOFWEEK, DAY, DAYOFYEAR, WEEK(begins on Sunday), ISOWEEK(begin on Monday), MONTH, QUARTER, YEAR, ISOYEAR, DATE, or TIME (See more details)
integer: the number of date_part
datetime: a DATETIME object or column
Return Data Type
DATETIME
Example
DATETIME_ADD(DATETIME("2020-12-25 15:30:00"), INTERVAL 10 MINUTE)
Result: 2020-12-25T15:40:00
DATETIME_SUB
DATETIME_SUB(datetime, INTERVAL integer datetime_part)
Description
Subtract a specified time interval to a given datetime. 
Input
date_part: can be one of the following: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR (See more details)
integer: the number of date_part
datetime: a DATETIME object or column
Return Data Type
DATETIME
Example
DATETIME_SUB(DATETIME("2020-12-25 15:30:00"), INTERVAL 10 MINUTE)
Result: 2020-12-25T15:20:00
DATETIME_DIFF
DATETIME_DIFF(datetime1, datetime2, date_part)
Description
Return the difference between two datetimes (dateime1-datetime2). The difference is expressed in units of date_part.
Input
datetime1: a DATETIME object or column
datetime2: a DATETIME object or column
date_part: can be one of the following: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEK(begins on Sunday), ISOWEEK(begins on Monday), MONTH, QUARTER, YEAR, or ISOYEAR (See more details)
Return Data Type
INTEGER
Example
DATETIME_DIFF(DATETIME("2020-12-25 10:20:00"), DATETIME("2020-12-20 15:30:00"), DAY)
Result: 5
DATETIME_TRUNC
DATETIME_TRUNC(datetime, date_part)
Description
Truncate a specific datetime to the start of a given unit of duration and returns a new datetime. The unit of duration is defined by date_part.
Input
datetime: a DATETIME object or column
date_part: can be one of the following: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEK, WEEK(), ISOWEEK, MONTH, QUARTER, YEAR, or ISOYEAR (See more details)
Return Data Type
DATETIME
Example
DATETIME_TRUNC(DATETIME("2021-04-10 15:30:00"), HOUR)
Result: 2021-04-10T15:00:00
DATETIME_TRUNC(DATETIME("2021-04-10 15:30:00"), MONTH)
Result: 2021-04-01T00:00:00
FORMAT_DATETIME
FORMAT_DATETIME(format, datetime)
Description
Transform a datetime variable into a string variable in a specific format. 
Input
format: a STRING expression.  To see supported format elements, visit here.
datetime: a DATETIME object or column.
Return Data Type
STRING
Example
FORMAT_DATETIME("%b %Y", DATETIME("2020-12-25 15:30:00"))
Result: Dec 2020
PARSE_DATETIME
PARSE_DATETIME(format, datetime_expression)
Description
Convert a string to a datetime variable according to the specified format. 
Input
format: a STRING expression.  To see supported format elements, visit here.
datetime_expression: a STRING object or column.
Return Data Type
DATETIME
Example
PARSE_DATETIME('%m/%d/%Y %I:%M:%S %p', '8/30/2020 2:23:38 PM')
Result: 2020-08-30T14:23:38
LAST_DAY
LAST_DAY(datetime, [date_part])
Description
Extract the last day of a given unit of duration for a given date. The unit of duration is defined by date_part. This is commonly used to return the last day of the month.
datetime: a DATETIME object or column
date_part: can be one of the following: YEAR, QUARTER, MONTH, WEEK, WEEK(), ISOWEEK, or ISOYEAR (See more details)
Return Data Type
DATETIME
Example
LAST_DAY(DATE('2021-03-25'), MONTH)
Result: 2021-03-31
LAST_DAY(DATE('2021-03-25'), WEEK)
Result: 2021-03-27
Details about date_part
Part
Description
MICROSECOND
The microsecond of the time.
MILLISECOND
The millisecond of the time.
SECOND
The second of the time.
MINUTE
The minute of the time.
HOUR
The hour of the time.
DAYOFWEEK
The weekday from 1 to 7 with Sunday as the first day of the week.
DAY
The day of the month.
DAYOFYEAR
The day of the year.
WEEK
The week number of the date from 0 to 53. (Weeks begin with Sunday, and dates prior to the first Sunday of the year are in week 0)
WEEK(<WEEKDAY>):
The week number of the date from 0 to 53. (You can optionally specify the first weekday of a week and weeks begin with the weekday that you specified. Dates prior to the first <WEEKDAY> of the year are in week 0. Valid values for <WEEKDAY> are SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, and SATURDAY.)
ISOWEEK
Returns the ISO 8601 week number of the date from 1 to 53 with Monday as the first day of the week. (The first ISOWEEK of each ISO year begins on the Monday before the first Thursday of the Gregorian calendar year.)
MONTH
Return the month of the date.
QUARTER
Return values in the range between 1 and 4.
YEAR
Return the month of the year.
ISOYEAR
Return the ISO 8601 week-numbering year, which is the Gregorian calendar year containing the Thursday of the week to which date_expression belongs.
DATE
Return the DATE object in a format of "yyyy-mm-dd".
TIME
Return the TIME object in a format of "HH:MM:SS".
Supported Format Elements
Format element
Description
Example
%A
Return the full weekday name.
Tuesday
%a
Return the abbreviated weekday name.
Tue
%B
Return the full name of the month.
May
%b or %h
Return the abbreviated name of the month.
May
%C
Return the century
20
%c
Return the date and time in a format of "%a %b %e %T %Y"
Tue May 4 15:31:13 2021
%D
Return the date in a format of "%m/%d/%y".
05/04/21
%d
Return the day of the month.
04
%e
Return the day of the month without leading 0.
4
%F
Return the date in a format of "%Y-%m-%d"
2021-05-04
%G
Return the ISO 8601 year with the century. The ISO 8601 year begins on the Monday before the first Thursday of the Gregorian calendar year.
2021
%g
Return the ISO 8601 year without the century. The ISO 8601 year begins on the Monday before the first Thursday of the Gregorian calendar year.
21
%H
Return the hour in the 24-hour clock (00-23).
15
%I
Return the hour in the 12-hour clock (01-12).
03
%j
Return the day of the year (001-366).
124
%k
Return the hour (0-23) in the 24-hour clock without leading 0.
15
%l
Return the hour (1-12) in the 12-hour clock without leading 0
3
%M
Return the minute of the time (00-59).
31
%m
Return the month of the date (01-12).
05
%n
Return a newline character.
​
%P
Return a lowercase "am" or "pm".
pm
%p
Return an uppercase "AM" or "PM".
PM
%Q
Return the quarter of the date (1-4).
2
%R
Return the time in a format of "%H:%M".
15:31
%r
Return the time in the 12-hour clock with AM/PM notation.
03:31:13 PM
%S
Return the second of the time (00-60).
13
%s
Return the number of seconds since 1970-01-01 00:00:00 UTC.
1620142273
%T
Return the time in a format of %H:%M:%S.
15:31:13
%t
Return a tab character.
​
%U
Return the week number (00-53) of the year. Sunday is the first day of the week
18
%u
Return the weekday of the date as 1-7. Monday is the first day of the week.
2
%V
Return the ISO 8601 week number (01-53) of the year. Monday is the first day of the week. If the week containing January 1 has four or more days in the new year, then it returns "week 1". Otherwise, it returns "week 53" of the previous year, and considers the next week as "week 1".
18
%W
Return the week number(00-53) of the year. Monday is the first day of the week.
18
%w
Return the weekday of the date as 0-6. Sunday is the first day of the week.
2
%X
Return the time in a format of "HH:MM:SS"
15:31:13
%x
Return the date in a format of "MM/DD/YY"
05/04/21
%Y
Return the year with the century
2021
%y
Return the year (00-99) without the century with an optional leading zero. Can be mixed with %C. If %C is not specified, years 00-68 are the 2000s, while years 69-99 are the 1900s.
21
%%
Return a single "%" character.
%
%E#S
Return the seconds with the given number of digits of fractional precision. The "#" should be replaced by a number.
"%E3S" returns
37.021
%E*S
Return the seconds with full fractional precision.
37.021851
%E4Y
Return the year in 4 digits (0000-9999).
2021
​

Date Functions

Time Functions

Last modified 1yr ago

Part	Description
MICROSECOND	The microsecond of the time.
MILLISECOND	The millisecond of the time.
SECOND	The second of the time.
MINUTE	The minute of the time.
HOUR	The hour of the time.
DAYOFWEEK	The weekday from 1 to 7 with Sunday as the first day of the week.
DAY	The day of the month.
DAYOFYEAR	The day of the year.
WEEK	The week number of the date from 0 to 53. (Weeks begin with Sunday, and dates prior to the first Sunday of the year are in week 0)
WEEK(<WEEKDAY>):	The week number of the date from 0 to 53. (You can optionally specify the first weekday of a week and weeks begin with the weekday that you specified. Dates prior to the first <WEEKDAY> of the year are in week 0. Valid values for <WEEKDAY> are SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, and SATURDAY.)
ISOWEEK	Returns the ISO 8601 week number of the date from 1 to 53 with Monday as the first day of the week. (The first ISOWEEK of each ISO year begins on the Monday before the first Thursday of the Gregorian calendar year.)
MONTH	Return the month of the date.
QUARTER	Return values in the range between 1 and 4.
YEAR	Return the month of the year.
ISOYEAR	Return the ISO 8601 week-numbering year, which is the Gregorian calendar year containing the Thursday of the week to which date_expression belongs.
DATE	Return the `DATE` object in a format of "yyyy-mm-dd".
TIME	Return the `TIME` object in a format of "HH:MM:SS".

Format element	Description	Example
%A	Return the full weekday name.	Tuesday
%a	Return the abbreviated weekday name.	Tue
%B	Return the full name of the month.	May
%b or %h	Return the abbreviated name of the month.	May
%C	Return the century	20
%c	Return the date and time in a format of "%a %b %e %T %Y"	Tue May 4 15:31:13 2021
%D	Return the date in a format of "%m/%d/%y".	05/04/21
%d	Return the day of the month.	04
%e	Return the day of the month without leading 0.	4
%F	Return the date in a format of "%Y-%m-%d"	2021-05-04
%G	Return the ISO 8601 year with the century. The ISO 8601 year begins on the Monday before the first Thursday of the Gregorian calendar year.	2021
%g	Return the ISO 8601 year without the century. The ISO 8601 year begins on the Monday before the first Thursday of the Gregorian calendar year.	21
%H	Return the hour in the 24-hour clock (00-23).	15
%I	Return the hour in the 12-hour clock (01-12).	03
%j	Return the day of the year (001-366).	124
%k	Return the hour (0-23) in the 24-hour clock without leading 0.	15
%l	Return the hour (1-12) in the 12-hour clock without leading 0	3
%M	Return the minute of the time (00-59).	31
%m	Return the month of the date (01-12).	05
%n	Return a newline character.
%P	Return a lowercase "am" or "pm".	pm
%p	Return an uppercase "AM" or "PM".	PM
%Q	Return the quarter of the date (1-4).	2
%R	Return the time in a format of "%H:%M".	15:31
%r	Return the time in the 12-hour clock with AM/PM notation.	03:31:13 PM
%S	Return the second of the time (00-60).	13
%s	Return the number of seconds since 1970-01-01 00:00:00 UTC.	1620142273
%T	Return the time in a format of %H:%M:%S.	15:31:13
%t	Return a tab character.
%U	Return the week number (00-53) of the year. Sunday is the first day of the week	18
%u	Return the weekday of the date as 1-7. Monday is the first day of the week.	2
%V	Return the ISO 8601 week number (01-53) of the year. Monday is the first day of the week. If the week containing January 1 has four or more days in the new year, then it returns "week 1". Otherwise, it returns "week 53" of the previous year, and considers the next week as "week 1".	18
%W	Return the week number(00-53) of the year. Monday is the first day of the week.	18
%w	Return the weekday of the date as 0-6. Sunday is the first day of the week.	2
%X	Return the time in a format of "HH:MM:SS"	15:31:13
%x	Return the date in a format of "MM/DD/YY"	05/04/21
%Y	Return the year with the century	2021
%y	Return the year (00-99) without the century with an optional leading zero. Can be mixed with %C. If %C is not specified, years 00-68 are the 2000s, while years 69-99 are the 1900s.	21
%%	Return a single "%" character.	%
%E#S	Return the seconds with the given number of digits of fractional precision. The "#" should be replaced by a number.	"%E3S" returns 37.021
%E*S	Return the seconds with full fractional precision.	37.021851
%E4Y	Return the year in 4 digits (0000-9999).	2021