Date Functions

CURRENT_DATE

CURRENT_DATE([timezone]) Returns current date in the timezone time zone (default UTC). Return value is of date type.

SELECT CURRENT_DATE()

SELECT CURRENT_DATE('America/Los_Angeles')

SELECT TYPEOF(CURRENT_DATE())

SELECT CAST(CURRENT_DATE() AS string)

SELECT FORMAT_DATE('%Y-%h-%d', CURRENT_DATE())

Here are some examples of comparison and interval arithmetic operations on the date type:

SELECT CURRENT_DATE() > PARSE_DATE('%Y-%m-%d', '2019-01-01')

SELECT CURRENT_DATE() > CURRENT_DATE() - INTERVAL 1 DAY

SELECT
    CURRENT_DATE() AS today,
    (CURRENT_DATE() - INTERVAL 1 WEEK) AS last_week

DATE Constructor

DATE(year, month, day)

Constructs value of type date based on year, month, and day.

SELECT TYPEOF(DATE(2019,8,17))

SELECT DATE(2019,8,17)

SELECT CAST(DATE(2019,8,17) AS string)

SELECT FORMAT_DATE('%Y-%h-%d', DATE(2019,8,17))

Here are some examples of comparison and interval arithmetic operations on the date type:

SELECT CURRENT_DATE() > DATE(2019,1,1)

SELECT CURRENT_DATE() > DATE(2019,8,17) + INTERVAL 1 DAY

SELECT
    DATE(2019,8,19) AS today,
    (DATE(2019,8,19) - INTERVAL 7 DAY) AS last_week

SELECT TYPEOF(DATE(CURRENT_DATETIME()))

SELECT DATE(CURRENT_DATETIME())

SELECT CAST(DATE(CURRENT_DATETIME()) AS string)

SELECT FORMAT_DATE('%Y-%h-%d', DATE(CURRENT_DATETIME()))

Here are some examples of comparison and interval arithmetic operations on the date type:

SELECT DATE(CURRENT_DATETIME()) > DATE(2019,1,1)

SELECT DATE(CURRENT_DATETIME()) > DATE(2019,8,19) - INTERVAL 1 DAY

SELECT
    DATE(2019,8,19) AS today,
    DATE(2019,8,19) - INTERVAL 7 DAY AS last_week

CURRENT_DATE([timezone]) Returns current date in the timezone time zone (default UTC). Return value is of date type.

SELECT TYPEOF(DATE(CURRENT_TIMESTAMP()))

SELECT DATE(CURRENT_TIMESTAMP())

SELECT DATE(CURRENT_TIMESTAMP(), 'Australia/Sydney')

SELECT CAST(DATE(CURRENT_TIMESTAMP()) AS string)

SELECT FORMAT_DATE('%Y-%h-%d', DATE(CURRENT_TIMESTAMP()))

Here are some examples of comparison and interval arithmetic operations on the date type:

SELECT DATE(CURRENT_TIMESTAMP()) > DATE(2019,1,1)

SELECT DATE(CURRENT_TIMESTAMP()) > DATE(2019,8,19) - INTERVAL 1 DAY

SELECT
    DATE(2019,8,19) AS today,
    (DATE(CURRENT_TIMESTAMP()) - INTERVAL 7 DAY) AS last_week

DATE_PARSE

DATE_PARSE(string, format) Parses the date string (formatted using the given format) into a date value.

The format specification is the standard strptime with CCTZ extensions. This function behaves exactly like PARSE_DATE but accepts parameters in different order. It exists for compatibility with DATE_PARSE function in Presto.

FORMAT_DATE

FORMAT_DATE(format, date) Converts date to string formatted using the given format.

The format specification is the standard strftime with CCTZ extensions.

 SELECT FORMAT_DATE('%Y/%m/%d', DATE '2018-05-26')

SELECT FORMAT_DATE('%Y-%h-%d', DATE '2019-8-19')

FORMAT_ISO8601 date

FORMAT_ISO8601(date)

Converts a date value to string using the ISO 8601 extended format. Returns string formatted using YYYY-mm-dd as the format specifier for years between 0000 and 9999. For negative years (before 1 BCE = year 0), we use a leading - sign. For years greater than 9999, we use a leading + sign.

SELECT FORMAT_ISO8601(DATE '-0427-00-00')

SELECT FORMAT_ISO8601(DATE '2019-01-01')

SELECT FORMAT_ISO8601(DATE '10000-01-01')

PARSE_DATE

PARSE_DATE(format, string) Parses the date string (formatted using the given format) into a date value.

The format specification is the standard strptime with CCTZ extensions.

SELECT PARSE_DATE('%Y-%m-%d', '2019-8-19')

SELECT TYPEOF(PARSE_DATE('%Y-%m-%d', '2019-8-19'))

Here are some examples of comparison and interval arithmetic operations on the date type:

SELECT DATE(2019,8,19) > PARSE_DATE('%Y/%m/%d', '2019/8/17')

SELECT DATE(2019,8,19) > PARSE_DATE('%Y/%m/%d', '2019/8/19') - INTERVAL 1 DAY

SELECT
    PARSE_DATE('%Y/%m/%d', '2019/8/19') AS today,
    (PARSE_DATE('%Y/%m/%d', '2019/8/19') - INTERVAL 7 DAY) AS last_week

PARSE_DATE_ISO8601

PARSE_DATE_ISO8601(string) Parses a date from an ISO 8601 string without a timezone.

SELECT PARSE_DATE_ISO8601('2019-8-17')

SELECT TYPEOF(PARSE_DATE_ISO8601('2019-8-19'))

Here are some examples of comparison and interval arithmetic operations on the date type:

SELECT DATE(2019,8,19) > PARSE_DATE_ISO8601('2019-8-17')

SELECT DATE '2019-08-19' > PARSE_DATE_ISO8601('2019-08-19') - INTERVAL 1 DAY

SELECT
    PARSE_DATE_ISO8601('2019-08-19') AS today,
    (PARSE_DATE_ISO8601('2019-8-19') - INTERVAL 7 DAY) AS last_week

DATE_FROM_UNIX_DATE

DATE_FROM_UNIX_DATE(number) Returns a date from the given number, the number of days since January 1st, 1970.

DATE_FROM_UNIX_DATE(unix_date)

Returns a Date from the given unix_date, the number of days since January 1st, 1970.

SELECT DATE_FROM_UNIX_DATE(18125)

SELECT TYPEOF(DATE_FROM_UNIX_DATE(18127))

Here are some examples of comparison and interval arithmetic operations on the date type:

SELECT DATE(2019,8,19) > DATE_FROM_UNIX_DATE(18125)

SELECT DATE '2019-08-19' > DATE_FROM_UNIX_DATE(18127) - INTERVAL 1 DAY

SELECT
    DATE_FROM_UNIX_DATE(18127) AS today,
    (DATE_FROM_UNIX_DATE(18127) - INTERVAL 7 DAY) AS last_week

UNIX_DATE

UNIX_DATE(date) Given a date, returns an integer representing the number of days since January 1st, 1970.

UNIX_DATE(date)

Given a Date, returns an integer representing the number of days since January 1st, 1970.

SELECT UNIX_DATE(DATE(2019,8,19))

SELECT TYPEOF(UNIX_DATE(DATE(2019,8,19)))

EXTRACT date

EXTRACT(part FROM date)

Extracts a component specified by part from date.

Here are supported values for part for date along with example queries.

DATE_TRUNC (date)

DATE_TRUNC(precision, date)

DATE_TRUNC(precision, date) Truncates the provided date (assumed at timezone UTC) to the precision level provided. To use an alternate timezone use AT TIME ZONE.

The precision may be specified as a string (see examples below), a microsecond interval (INTERVAL x MICROSECOND, INTERVAL x DAY, etc), or a moth interval (INTERVAL x MONTH, INTERVAL x YEAR).

Here are supported values for precision (case-insensitive) along with example queries:

DATE_DIFF (date)

DATE_DIFF(precision, start_date, end_date) Computes the difference between start_date and end_date at the precision level provided. The difference is truncated to the nearest integer.

DATE_DIFF(precision, start_date, end_date)

Here are supported values for precision (case-insensitive) along with example queries:

Note: A MONTH is equal to 30 DAYs. A QUARTER is equal to 90 DAYs. A YEAR is equal to 365 DAYs.