GoogleSQL for BigQuery supports the following date functions.

Function list

CURRENT_DATE

CURRENT_DATE()

CURRENT_DATE(time_zone_expression)

CURRENT_DATE

Description

Returns the current date as aDATE object. Parentheses are optional whencalled with no arguments.

This function supports the following arguments:

• time_zone_expression: ASTRING expression that represents atime zone. If no time zone is specified, thedefault time zone, UTC, is used. If this expression isused and it evaluates toNULL, this function returnsNULL.

The current date value is set at the start of the query statement that containsthis function. All invocations ofCURRENT_DATE() within a query statementyield the same value.

Return Data Type

DATE

Examples

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

SELECTCURRENT_DATE()ASthe_date;/*--------------+ | the_date     | +--------------+ | 2016-12-25   | +--------------*/

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

SELECTCURRENT_DATE('America/Los_Angeles')ASthe_date;/*--------------+ | the_date     | +--------------+ | 2016-12-25   | +--------------*/

SELECTCURRENT_DATE('-08')ASthe_date;/*--------------+ | the_date     | +--------------+ | 2016-12-25   | +--------------*/

The following query produces the current date in the default time zone.Parentheses aren't needed if the function has no arguments.

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

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: TheINT64 value for year.
• month: TheINT64 value for month.
• day: TheINT64 value for day.
• timestamp_expression: ATIMESTAMP expression that contains the date.
• time_zone_expression: ASTRING expression that represents atime zone. If no time zone is specified withtimestamp_expression, the default time zone, UTC, isused.
• datetime_expression: ADATETIME expression that contains the date.

Return Data Type

DATE

Example

SELECTDATE(2016,12,25)ASdate_ymd,DATE(DATETIME'2016-12-25 23:59:59')ASdate_dt,DATE(TIMESTAMP'2016-12-25 05:30:00+07','America/Los_Angeles')ASdate_tstz;/*------------+------------+------------+ | date_ymd   | date_dt    | date_tstz  | +------------+------------+------------+ | 2016-12-25 | 2016-12-25 | 2016-12-24 | +------------+------------+------------*/

DATE_ADD

DATE_ADD(date_expression,INTERVALint64_expressiondate_part)

Description

Adds a specified time interval to a DATE.

DATE_ADD supports the followingdate_part values:

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

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

Return Data Type

DATE

Example

SELECTDATE_ADD(DATE'2008-12-25',INTERVAL5DAY)ASfive_days_later;/*--------------------+ | five_days_later    | +--------------------+ | 2008-12-30         | +--------------------*/

DATE_DIFF

DATE_DIFF(end_date,start_date,granularity)

Description

Gets the number of unit boundaries between twoDATE values (end_date -start_date) at a particular time granularity.

Definitions

• start_date: The startingDATE value.
• end_date: The endingDATE value.

• granularity: The date part that represents the granularity. Ifyou have passed inDATE values for the first arguments,granularity canbe:

  • DAY
  • WEEK This date part begins on Sunday.
  • WEEK(<WEEKDAY>): This date part begins onWEEKDAY. Valid values forWEEKDAY areSUNDAY,MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY, andSATURDAY.
  • ISOWEEK: UsesISO 8601 week boundaries. ISO weeksbegin on Monday.
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: Uses theISO 8601 week-numbering year boundary.The ISO year boundary is the Monday of the first week whose Thursdaybelongs to the corresponding Gregorian calendar year.

Details

Ifend_date is earlier thanstart_date, the output is negative.

SELECTDATE_DIFF(DATE'2010-07-07',DATE'2008-12-25',DAY)ASdays_diff;/*-----------+ | days_diff | +-----------+ | 559       | +-----------*/

SELECTDATE_DIFF(DATE'2017-10-15',DATE'2017-10-14',DAY)ASdays_diff,DATE_DIFF(DATE'2017-10-15',DATE'2017-10-14',WEEK)ASweeks_diff;/*-----------+------------+ | days_diff | weeks_diff | +-----------+------------+ | 1         | 1          | +-----------+------------*/

SELECTDATE_DIFF('2017-12-30','2014-12-30',YEAR)ASyear_diff,DATE_DIFF('2017-12-30','2014-12-30',ISOYEAR)ASisoyear_diff;/*-----------+--------------+ | year_diff | isoyear_diff | +-----------+--------------+ | 3         | 2            | +-----------+--------------*/

SELECTDATE_DIFF('2017-12-18','2017-12-17',WEEK)ASweek_diff,DATE_DIFF('2017-12-18','2017-12-17',WEEK(MONDAY))ASweek_weekday_diff,DATE_DIFF('2017-12-18','2017-12-17',ISOWEEK)ASisoweek_diff;/*-----------+-------------------+--------------+ | week_diff | week_weekday_diff | isoweek_diff | +-----------+-------------------+--------------+ | 0         | 1                 | 1            | +-----------+-------------------+--------------*/

DATE_FROM_UNIX_DATE(int64_expression)

SELECTDATE_FROM_UNIX_DATE(14238)ASdate_from_epoch;/*-----------------+ | date_from_epoch | +-----------------+ | 2008-12-25      | +-----------------+*/

DATE_SUB(date_expression,INTERVALint64_expressiondate_part)

SELECTDATE_SUB(DATE'2008-12-25',INTERVAL5DAY)ASfive_days_ago;/*---------------+ | five_days_ago | +---------------+ | 2008-12-20    | +---------------*/

DATE_TRUNC(date_value,date_granularity)

DATE_TRUNC(datetime_value,datetime_granularity)

DATE_TRUNC(timestamp_value,timestamp_granularity[,time_zone])

• date_value: ADATE value to truncate.
• date_granularity: The truncation granularity for aDATE value.Date granularities can be used.
• datetime_value: ADATETIME value to truncate.
• datetime_granularity: The truncation granularity for aDATETIME value.Date granularities andtime granularities can be used.
• timestamp_value: ATIMESTAMP value to truncate.
• timestamp_granularity: The truncation granularity for aTIMESTAMP value.Date granularities andtime granularities can be used.

• time_zone: A time zone to use with theTIMESTAMP value.Time zone parts can be used.Use this argument if you want to use a time zone other thanthe default time zone, UTC, as part of thetruncate operation.

  Note: When truncating a timestamp toMINUTEorHOUR parts, this function determines the civil time of thetimestamp in the specified (or default) time zoneand subtracts the minutes and seconds (when truncating toHOUR) or theseconds (when truncating toMINUTE) from that timestamp.While this provides intuitive results in most cases, the result isnon-intuitive near daylight savings transitions that aren't hour-aligned.

Date granularity definitions

• DAY: The day in the Gregorian calendar year that contains thevalue to truncate.

• WEEK: The first day in the week that contains thevalue to truncate. Weeks begin on Sundays.WEEK is equivalent toWEEK(SUNDAY).

• WEEK(WEEKDAY): The first day in the week that contains thevalue to truncate. Weeks begin onWEEKDAY.WEEKDAY must be one of the following:SUNDAY,MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY, orSATURDAY.

• ISOWEEK: The first day in theISO 8601 week that containsthe value to truncate. The ISO week begins onMonday. The first ISO week of each ISO year contains the first Thursday of thecorresponding Gregorian calendar year.

• MONTH: The first day in the month that contains thevalue to truncate.

• QUARTER: The first day in the quarter that contains thevalue to truncate.

• YEAR: The first day in the year that contains thevalue to truncate.

• ISOYEAR: The first day in theISO 8601 week-numbering yearthat contains the value to truncate. The ISO year is theMonday of the first week where Thursday belongs to the correspondingGregorian calendar year.

Time granularity definitions

• MICROSECOND: If used, nothing is truncated from the value.

• MILLISECOND: The nearest lesser than or equal millisecond.

• SECOND: The nearest lesser than or equal second.

• MINUTE: The nearest lesser than or equal minute.

• HOUR: The nearest lesser than or equal hour.

Time zone part definitions

• MINUTE
• HOUR
• DAY
• WEEK
• WEEK(<WEEKDAY>)
• ISOWEEK
• MONTH
• QUARTER
• YEAR
• ISOYEAR

Details

The resulting value is always rounded to the beginning ofgranularity.

Return Data Type

The same data type as the first argument passed into this function.

Examples

SELECTDATE_TRUNC(DATE'2008-12-25',MONTH)ASmonth;/*------------+ | month      | +------------+ | 2008-12-01 | +------------*/

In the following example, the original date falls on a Sunday. Becausethedate_part isWEEK(MONDAY),DATE_TRUNC returns theDATE for thepreceding Monday.

SELECTdateASoriginal,DATE_TRUNC(date,WEEK(MONDAY))AStruncatedFROM(SELECTDATE('2017-11-05')ASdate);/*------------+------------+ | original   | truncated  | +------------+------------+ | 2017-11-05 | 2017-10-30 | +------------+------------*/

In the following example, the originaldate_expression is in the Gregoriancalendar year 2015. However,DATE_TRUNC with theISOYEAR date parttruncates thedate_expression to the beginning of the ISO year, not theGregorian calendar year. The first Thursday of the 2015 calendar year was2015-01-01, so the ISO year 2015 begins on the preceding Monday, 2014-12-29.Therefore the ISO year boundary preceding thedate_expression 2015-06-15 is2014-12-29.

SELECTDATE_TRUNC('2015-06-15',ISOYEAR)ASisoyear_boundary,EXTRACT(ISOYEARFROMDATE'2015-06-15')ASisoyear_number;/*------------------+----------------+ | isoyear_boundary | isoyear_number | +------------------+----------------+ | 2014-12-29       | 2015           | +------------------+----------------*/

EXTRACT

EXTRACT(partFROMdate_expression)

Description

Returns the value corresponding to the specified date part. Thepart mustbe one of:

• DAYOFWEEK: Returns values in the range [1,7] with Sunday as the first dayof the week.
• DAY
• DAYOFYEAR
• WEEK: Returns the week number of the date in the range [0, 53]. Weeks beginwith Sunday, and dates prior to the first Sunday of the year are in week0.
• WEEK(<WEEKDAY>): Returns the week number of the date in the range [0, 53].Weeks begin onWEEKDAY. Dates prior tothe firstWEEKDAY of the year are in week 0. Valid values forWEEKDAY areSUNDAY,MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY, andSATURDAY.
• ISOWEEK: Returns theISO 8601 weeknumber of thedate_expression.ISOWEEKs begin on Monday. Return valuesare in the range [1, 53]. The firstISOWEEK of each ISO year begins on theMonday before the first Thursday of the Gregorian calendar year.
• MONTH
• QUARTER: Returns values in the range [1,4].
• YEAR
• ISOYEAR: Returns theISO 8601week-numbering year, which is the Gregorian calendar year containing theThursday of the week to whichdate_expression belongs.

Return Data Type

INT64

Examples

In the following example,EXTRACT returns a value corresponding to theDAYdate part.

SELECTEXTRACT(DAYFROMDATE'2013-12-25')ASthe_day;/*---------+ | the_day | +---------+ | 25      | +---------*/

In the following example,EXTRACT returns values corresponding to differentdate parts from a column of dates near the end of the year.

SELECTdate,EXTRACT(ISOYEARFROMdate)ASisoyear,EXTRACT(ISOWEEK FROMdate)ASisoweek,EXTRACT(YEARFROMdate)ASyear,EXTRACT(WEEKFROMdate)ASweekFROMUNNEST(GENERATE_DATE_ARRAY('2015-12-23','2016-01-09'))ASdateORDERBYdate;/*------------+---------+---------+------+------+ | date       | isoyear | isoweek | year | week | +------------+---------+---------+------+------+ | 2015-12-23 | 2015    | 52      | 2015 | 51   | | 2015-12-24 | 2015    | 52      | 2015 | 51   | | 2015-12-25 | 2015    | 52      | 2015 | 51   | | 2015-12-26 | 2015    | 52      | 2015 | 51   | | 2015-12-27 | 2015    | 52      | 2015 | 52   | | 2015-12-28 | 2015    | 53      | 2015 | 52   | | 2015-12-29 | 2015    | 53      | 2015 | 52   | | 2015-12-30 | 2015    | 53      | 2015 | 52   | | 2015-12-31 | 2015    | 53      | 2015 | 52   | | 2016-01-01 | 2015    | 53      | 2016 | 0    | | 2016-01-02 | 2015    | 53      | 2016 | 0    | | 2016-01-03 | 2015    | 53      | 2016 | 1    | | 2016-01-04 | 2016    | 1       | 2016 | 1    | | 2016-01-05 | 2016    | 1       | 2016 | 1    | | 2016-01-06 | 2016    | 1       | 2016 | 1    | | 2016-01-07 | 2016    | 1       | 2016 | 1    | | 2016-01-08 | 2016    | 1       | 2016 | 1    | | 2016-01-09 | 2016    | 1       | 2016 | 1    | +------------+---------+---------+------+------*/

In the following example,date_expression falls on a Sunday.EXTRACTcalculates the first column using weeks that begin on Sunday, and it calculatesthe second column using weeks that begin on Monday.

WITHtableAS(SELECTDATE('2017-11-05')ASdate)SELECTdate,EXTRACT(WEEK(SUNDAY)FROMdate)ASweek_sunday,EXTRACT(WEEK(MONDAY)FROMdate)ASweek_mondayFROMtable;/*------------+-------------+-------------+ | date       | week_sunday | week_monday | +------------+-------------+-------------+ | 2017-11-05 | 45          | 44          | +------------+-------------+-------------*/

FORMAT_DATE

FORMAT_DATE(format_string,date_expr)

Description

Formats aDATE value according to a specified format string.

Definitions

• format_string: ASTRING value that contains theformat elements to use withdate_expr.
• date_expr: ADATE value that represents the date to format.

Return Data Type

STRING

Examples

SELECTFORMAT_DATE('%x',DATE'2008-12-25')ASUS_format;/*------------+ | US_format  | +------------+ | 12/25/08   | +------------*/

SELECTFORMAT_DATE('%b-%d-%Y',DATE'2008-12-25')ASformatted;/*-------------+ | formatted   | +-------------+ | Dec-25-2008 | +-------------*/

SELECTFORMAT_DATE('%b %Y',DATE'2008-12-25')ASformatted;/*-------------+ | formatted   | +-------------+ | Dec 2008    | +-------------*/

LAST_DAY

LAST_DAY(date_expression[,date_part])

Description

Returns the last day from a date expression. This is commonly used to returnthe last day of the month.

You can optionally specify the date part for which the last day is returned.If this parameter isn't used, the default value isMONTH.LAST_DAY supports the following values fordate_part:

• YEAR
• QUARTER
• MONTH
• WEEK. Equivalent to 7DAYs.
• WEEK(<WEEKDAY>).<WEEKDAY> represents the starting day of the week.Valid values areSUNDAY,MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY, andSATURDAY.
• ISOWEEK. UsesISO 8601 week boundaries. ISO weeks beginon Monday.
• ISOYEAR. Uses theISO 8601 week-numbering year boundary.The ISO year boundary is the Monday of the first week whose Thursday belongsto the corresponding Gregorian calendar year.

Return Data Type

DATE

Example

These both return the last day of the month:

SELECTLAST_DAY(DATE'2008-11-25',MONTH)ASlast_day/*------------+ | last_day   | +------------+ | 2008-11-30 | +------------*/

SELECTLAST_DAY(DATE'2008-11-25')ASlast_day/*------------+ | last_day   | +------------+ | 2008-11-30 | +------------*/

This returns the last day of the year:

SELECTLAST_DAY(DATE'2008-11-25',YEAR)ASlast_day/*------------+ | last_day   | +------------+ | 2008-12-31 | +------------*/

This returns the last day of the week for a week that starts on a Sunday:

SELECTLAST_DAY(DATE'2008-11-10',WEEK(SUNDAY))ASlast_day/*------------+ | last_day   | +------------+ | 2008-11-15 | +------------*/

This returns the last day of the week for a week that starts on a Monday:

SELECTLAST_DAY(DATE'2008-11-10',WEEK(MONDAY))ASlast_day/*------------+ | last_day   | +------------+ | 2008-11-16 | +------------*/

PARSE_DATE

PARSE_DATE(format_string,date_string)

Description

Converts aSTRING value to aDATE value.

Definitions

• format_string: ASTRING value that contains theformat elements to use withdate_string.
• date_string: ASTRING value that represents the date to parse.

Details

Each element indate_string must have a corresponding element informat_string. The location of each element informat_string must match thelocation of each element indate_string.

-- This works because elements on both sides match.SELECTPARSE_DATE('%A %b %e %Y','Thursday Dec 25 2008');-- This produces an error because the year element is in different locations.SELECTPARSE_DATE('%Y %A %b %e','Thursday Dec 25 2008');-- This produces an error because one of the year elements is missing.SELECTPARSE_DATE('%A %b %e','Thursday Dec 25 2008');-- This works because %F can find all matching elements in date_string.SELECTPARSE_DATE('%F','2000-12-30');

The following additional considerations apply when using thePARSE_DATEfunction:

• Unspecified fields. Any unspecified field is initialized from1970-01-01.
• Case insensitivity. Names, such asMonday,February, and so on, arecase insensitive.
• Whitespace. One or more consecutive white spaces in the format stringmatches zero or more consecutive white spaces in the date string. Inaddition, leading and trailing white spaces in the date string are alwaysallowed, even if they aren't in the format string.
• Format precedence. When two (or more) format elements have overlappinginformation (for example both%F and%Y affect the year), the last onegenerally overrides any earlier ones.
• Mixed ISO and non-ISO elements. The ISO format elements are%G,%g,%J, and%V. When these ISO elements are used together with other non-ISOelements, the ISO elements are ignored, resulting in different values. Forexample, the function arguments('%g %J', '8405') return a value with theyear1984, whereas the arguments('%g %j', '8405') return a value withthe year1970 because the ISO element%g is ignored.
• Numeric values after%G input values. Any input string value thatcorresponds to the%G format element requires a whitespace or non-digitcharacter as a separator from numeric values that follow. This is a knownissue in GoogleSQL. For example, the function arguments('%G%V','2020 50') or('%G-%V','2020-50') work, but not('%G%V','202050').For input values before the corresponding%G value, no separator isneeded. For example, the arguments('%V%G','502020') work. The separatorafter the%G values identifies the end of the specified ISO year value sothat the function can parse properly.

Return Data Type

DATE

Examples

This example converts aMM/DD/YY formatted string to aDATE object:

SELECTPARSE_DATE('%x','12/25/08')ASparsed;/*------------+ | parsed     | +------------+ | 2008-12-25 | +------------*/

This example converts aYYYYMMDD formatted string to aDATE object:

SELECTPARSE_DATE('%Y%m%d','20081225')ASparsed;/*------------+ | parsed     | +------------+ | 2008-12-25 | +------------*/

UNIX_DATE

UNIX_DATE(date_expression)

Description

Returns the number of days since1970-01-01.

Return Data Type

INT64

Example

SELECTUNIX_DATE(DATE'2008-12-25')ASdays_from_epoch;/*-----------------+ | days_from_epoch | +-----------------+ | 14238           | +-----------------*/