GoogleSQL for Spanner supports the following date functions.

Function list

ADDDATE

ADDDATE(date_expression,INTERVALint64_expressiondate_part)

Alias forDATE_ADD.

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, America/Los_Angeles, 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)

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, America/Los_Angeles, isused.

Return Data Type

DATE

Example

SELECTDATE(2016,12,25)ASdate_ymd,DATE(TIMESTAMP'2016-12-25 05:30:00+07','America/Los_Angeles')ASdate_tstz;/*------------+------------+ | date_ymd   | date_tstz  | +------------+------------+ | 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. This can be:

  • DAY
  • WEEK This date part begins on Sunday.
  • 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.

Return Data Type

INT64

Example

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          | +-----------+------------*/

The example above shows the result ofDATE_DIFF for two days in succession.DATE_DIFF with the date partWEEK returns 1 becauseDATE_DIFF counts thenumber of date part boundaries in this range of dates. EachWEEK begins onSunday, so there is one date part boundary between Saturday, 2017-10-14and Sunday, 2017-10-15.

The following example shows the result ofDATE_DIFF for two dates in differentyears.DATE_DIFF with the date partYEAR returns 3 because it counts thenumber of Gregorian calendar year boundaries between the two dates.DATE_DIFFwith the date partISOYEAR returns 2 because the second date belongs to theISO year 2015. The first Thursday of the 2015 calendar year was 2015-01-01, sothe ISO year 2015 begins on the preceding Monday, 2014-12-29.

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            | +-----------+--------------*/

The following example shows the result ofDATE_DIFF for two days insuccession. The first date falls on a Monday and the second date falls on aSunday.DATE_DIFF with the date partWEEK returns 0 because this date partuses weeks that begin on Sunday.DATE_DIFF with the date partISOWEEKreturns 1 because ISO weeks begin on Monday.

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

DATE_FROM_UNIX_DATE

DATE_FROM_UNIX_DATE(int64_expression)

Description

Interpretsint64_expression as the number of days since 1970-01-01.

Return Data Type

DATE

Example

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

DATE_SUB

DATE_SUB(date_expression,INTERVALint64_expressiondate_part)

Description

Subtracts a specified time interval from a DATE.

DATE_SUB 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_SUB(DATE'2008-12-25',INTERVAL5DAY)ASfive_days_ago;/*---------------+ | five_days_ago | +---------------+ | 2008-12-20    | +---------------*/

DATE_TRUNC

DATE_TRUNC(date_value,date_granularity)

Description

Truncates aDATE value at a particular granularity.

Definitions

• date_value: ADATE value to truncate.
• date_granularity: The truncation granularity for aDATE value.Date granularities can be used.

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

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

Details

The resulting value is always rounded to the beginning ofgranularity.

Return Data Type

DATE

Examples

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

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.
• 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    | +------------+---------+---------+------+------*/

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    | +-------------*/

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 format string fully supports most format elements except for%g,%G,%j,%u,%U,%V,%w, and%W.

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.

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 | +------------*/

SUBDATE

SUBDATE(date_expression,INTERVALint64_expressiondate_part)

Alias forDATE_SUB.

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           | +-----------------*/