Date functions

GoogleSQL for BigQuery supports the following date functions.

Function list

Name Summary
CURRENT_DATE Returns the current date as a DATE value.
DATE Constructs a DATE value.
DATE_ADD Adds a specified time interval to a DATE value.
DATE_DIFF Gets the number of intervals between two DATE values.
DATE_FROM_UNIX_DATE Interprets an INT64 expression as the number of days since 1970-01-01.
DATE_SUB Subtracts a specified time interval from a DATE value.
DATE_TRUNC Truncates a DATE value.
EXTRACT Extracts part of a date from a DATE value.
FORMAT_DATE Formats a DATE value according to a specified format string.
LAST_DAY Gets the last day in a specified time period that contains a DATE value.
PARSE_DATE Converts a STRING value to a DATE value.
UNIX_DATE Converts a DATE value to the number of days since 1970-01-01.

CURRENT_DATE

CURRENT_DATE()
CURRENT_DATE(time_zone_expression)
CURRENT_DATE

Description

Returns the current date as a DATE object. Parentheses are optional when called with no arguments.

This function supports the following arguments:

  • time_zone_expression: A STRING expression that represents a time zone. If no time zone is specified, the default time zone, UTC, is used. If this expression is used and it evaluates to NULL, this function returns NULL.

The current date is recorded at the start of the query statement which contains this function, not when this specific function is evaluated.

Return Data Type

DATE

Examples

The following query produces the current date in the default time zone:

SELECT CURRENT_DATE() AS the_date;

/*--------------*
 | the_date     |
 +--------------+
 | 2016-12-25   |
 *--------------*/

The following queries produce the current date in a specified time zone:

SELECT CURRENT_DATE('America/Los_Angeles') AS the_date;

/*--------------*
 | the_date     |
 +--------------+
 | 2016-12-25   |
 *--------------*/
SELECT CURRENT_DATE('-08') AS the_date;

/*--------------*
 | the_date     |
 +--------------+
 | 2016-12-25   |
 *--------------*/

The following query produces the current date in the default time zone. Parentheses are not needed if the function has no arguments.

SELECT CURRENT_DATE AS the_date;

/*--------------*
 | the_date     |
 +--------------+
 | 2016-12-25   |
 *--------------*/

When a column named current_date is present, the column name and the function call without parentheses are ambiguous. To ensure the function call, add parentheses; to ensure the column name, qualify it with its range variable. For example, the following query will select the function in the the_date column and the table column in the current_date column.

WITH t AS (SELECT 'column value' AS `current_date`)
SELECT current_date() AS the_date, t.current_date FROM t;

/*------------+--------------*
 | the_date   | current_date |
 +------------+--------------+
 | 2016-12-25 | column value |
 *------------+--------------*/

DATE

DATE(year, month, day)
DATE(timestamp_expression)
DATE(timestamp_expression, time_zone_expression)
DATE(datetime_expression)

Description

Constructs or extracts a date.

This function supports the following arguments:

  • year: The INT64 value for year.
  • month: The INT64 value for month.
  • day: The INT64 value for day.
  • timestamp_expression: A TIMESTAMP expression that contains the date.
  • time_zone_expression: A STRING expression that represents a time zone. If no time zone is specified with timestamp_expression, the default time zone, UTC, is used.
  • datetime_expression: A DATETIME expression that contains the date.

Return Data Type

DATE

Example

SELECT
  DATE(2016, 12, 25) AS date_ymd,
  DATE(DATETIME '2016-12-25 23:59:59') AS date_dt,
  DATE(TIMESTAMP '2016-12-25 05:30:00+07', 'America/Los_Angeles') AS date_tstz;

/*------------+------------+------------*
 | date_ymd   | date_dt    | date_tstz  |
 +------------+------------+------------+
 | 2016-12-25 | 2016-12-25 | 2016-12-24 |
 *------------+------------+------------*/

DATE_ADD

DATE_ADD(date_expression, INTERVAL int64_expression date_part)

Description

Adds a specified time interval to a DATE.

DATE_ADD supports the following date_part values:

  • DAY
  • WEEK. Equivalent to 7 DAYs.
  • MONTH
  • QUARTER
  • YEAR

Special handling is required for MONTH, QUARTER, and YEAR parts when the date is at (or near) the last day of the month. If the resulting month has fewer days than the original date's day, then the resulting date is the last date of that month.

Return Data Type

DATE

Example

<