Date Functions

CURRENT_DATE

CURRENT_DATE([time_zone])

Description

Return the current date.

Input

  • timezone(optional, STRING): The default timezone is UTC. You can optionally specify a time zone.

Return Data Type

DATE

Example

CURRENT_DATE()

Result: 2021-04-26

CURRENT_DATE("America/New_York")

Result: 2021-04-25

DATE

Date(date_expression_column)

Description

Convert a string, timestamp, or datetime expression to a date variable in a "yyyy-mm-dd" format. For the timestamp inputs, you can optionally specify a time zone.

Input

A STRING, DATETIME, TIMESTAMP object or column

Return Data Type

DATE

Example

DATE("2016-12-25")

Result: 2016-12-25

DATE(DATETIME("2016-12-25 23:59:59"))

Result: 2016-12-25

DATE(TIMESTAMP("2016-12-25 05:30:00+07"), "America/Los_Angeles")

Result: 2016-12-24

EXTRACT

EXTRACT(date_part FROM date)

Description

Return a part of the date expression as an integer.

Input

  • date_part: DAY, WEEK(begins on Sunday), ISOWEEK(begin on Monday), MONTH, QUARTER, YEAR, or ISOYEAR (See more details)

  • date: a DATE object or column

Return Data Type

DATE

Example

EXTRACT(DAY FROM DATE("2020-12-25"))

Result: 25

DATE_ADD

DATE_ADD(date, INTERVAL integer date_part)

Description

Add a specified time interval to the given date.

Input

  • date_part: DAY, WEEK, MONTH, QUARTER, or YEAR (See more details)

  • integer: the number of date_part

  • date: a DATE object or column

Return Data Type

DATE

Example

DATE_ADD(DATE("2020-12-25"), INTERVAL 5 DAY)

Result: 2020-12-30

DATE_SUB

DATE_SUB(date, INTERVAL integer date_part)

Description

Add a specified time interval to the given date.

Input

  • date_part: DAY, WEEK, MONTH, QUARTER, or YEAR (See more details)

  • integer: the number of date_part

  • date: a DATE object or column

Return Data Type

DATE

Example

DATE_SUB(DATE("2020-12-25"), INTERVAL 5 DAY)

Result: 2020-12-20

DATE_DIFF

DATE_DIFF(date1, date2, date_part)

Description

Return the difference between two dates (date1-date2). The difference is expressed in units of date_part. The date_part can be .

Input

  • date1: a DATE object or column

  • date2: a DATE object or column

  • date_part: can be one of the following: DAY, WEEK(begins on Sunday), ISOWEEK(begins on Monday), MONTH, QUARTER, YEAR, or ISOYEAR (See more details)

Return Data Type

INTEGER

Example

DATE_DIFF(DATE("2017-10-15"), DATE("2017-10-13"), DAY)

Result: 2

DATE_TRUNC

DATE_TRUNC(date, date_part)

Description

Truncate a specific date to the start of a given unit of duration and returns a new date. The unit of duration is defined by date_part.

Input

  • date: a DATE object or column

  • date_part: can be one of the following: DAY, WEEK, WEEK(<weekday>), ISOWEEK, MONTH, QUARTER, YEAR, or ISOYEAR (See more details)

Return Data Type

DATE

Example

DATE_TRUNC(DATE( '2021-4-23'), MONTH)

Result: 2021-04-01

DATE_TRUNC(DATE('2021-4-23'), WEEK(MONDAY))

Result: 2021-04-19

DATE_FROM_UNIX_DATE

DATE_FROM_UNIX_DATE(integer)

Description

Transform a UNIX date expression (that is, the number of days since 1970-01-01) into a standard date format (yyyy-mm-dd).

Input

an INTEGER object or column

Return Data Type

DATE

Example

DATE_FROM_UNIX_DATE(18650)

Result: 2021-01-23

FORMAT_DATE

FORMAT_DATE(format, date)

Description

Transform a date variable into a string variable in a specific format.

Input

  • format: a STRING expression. To see supported format elements, visit here.

  • date: a DATE object or column

Return Data Type

STRING

Example

FORMAT_DATE("%b-%d-%Y", DATE("2008-12-25"))

Result: Dec-25-2008

PARSE_DATE

PARSE_DATE(format, date_expression)

Description

Convert a string to a date column according to the specified format.

Input

  • format: a STRING expression. To see supported format elements, visit here.

  • date_expression: a STRING object or column

Return Data Type

DATE

Example

PARSE_DATE("%A %b %e %Y", "Thursday Feb 11 2021")

Result: 2021-02-11

LAST_DAY

LAST_DAY(date, [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 date of the month.

Input

  • date_part: (optional) can be one of the following: YEAR, QUARTER, MONTH, WEEK, WEEK(), ISOWEEK, or ISOYEAR (See more details)

  • date: a DATE object or column

Return Data Type

DATE

Example

LAST_DAY(DATE('2021-03-25'), MONTH)

Result: 2021-03-31

LAST_DAY(DATE('2021-03-25'), WEEK)

Result; 2021-03-27

UNIX_DATE

UNIX_DATE(date)

Description

Return the number of days since 1970-01-01.

Input

a DATE object or a DATE column

Return Data Type

INTEGER

Example

UNIX_DATE(DATE("2020-12-25"))

Result: 18621

Details about date_part

Part

Description

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.

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

21

%Z

Return the time zone name.

UTC-5

%z

Return the offset from the Prime Meridian in the format +HHMM or -HHMM as appropriate, with positive values representing locations east of Greenwich.

\-0500

%%

Return a single "%" character.

%

%Ez

Return the RFC 3339-compatible numeric time zone (+HH:MM or -HH:MM).

\-05:00

%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

Last updated