Format elements in GoogleSQL

GoogleSQL for Spanner supports the following format elements.

Format elements for date and time parts

Many GoogleSQL parsing and formatting functions rely on a format string to describe the format of parsed or formatted values. A format string represents the textual form of date and time and contains separate format elements that are applied left-to-right.

These functions use format strings:

Format strings generally support the following elements:

Format element Type Description Example
%A DATE
TIMESTAMP
 The full weekday name (English). Wednesday
%a DATE
TIMESTAMP
 The abbreviated weekday name (English). Wed
%B DATE
TIMESTAMP
 The full month name (English). January
%b DATE
TIMESTAMP
 The abbreviated month name (English). Jan
%C DATE
TIMESTAMP
 The century (a year divided by 100 and truncated to an integer) as a decimal number (00-99). 20
%c TIMESTAMP
 The date and time representation (English). Wed Jan 20 21:47:00 2021
%D DATE
TIMESTAMP
 The date in the format %m/%d/%y. 01/20/21
%d DATE
TIMESTAMP
 The day of the month as a decimal number (01-31). 20
%e DATE
TIMESTAMP
 The day of month as a decimal number (1-31); single digits are preceded by a space. 20
%F DATE
TIMESTAMP
 The date in the format %Y-%m-%d. 2021-01-20
%G DATE
TIMESTAMP
 The ISO 8601 year with century as a decimal number. Each ISO year begins on the Monday before the first Thursday of the Gregorian calendar year. Note that %G and %Y may produce different results near Gregorian year boundaries, where the Gregorian year and ISO year can diverge. 2021
%g DATE
TIMESTAMP
 The ISO 8601 year without century as a decimal number (00-99). Each ISO year begins on the Monday before the first Thursday of the Gregorian calendar year. Note that %g and %y may produce different results near Gregorian year boundaries, where the Gregorian year and ISO year can diverge. 21
%H TIMESTAMP
 The hour (24-hour clock) as a decimal number (00-23). 21
%h DATE
TIMESTAMP
 The abbreviated month name (English). Jan
%I TIMESTAMP
 The hour (12-hour clock) as a decimal number (01-12). 09
%j DATE
TIMESTAMP
 The day of the year as a decimal number (001-366). 020
%k TIMESTAMP
 The hour (24-hour clock) as a decimal number (0-23); single digits are preceded by a space. 21
%l TIMESTAMP
 The hour (12-hour clock) as a decimal number (1-12); single digits are preceded by a space.  9
%M TIMESTAMP
 The minute as a decimal number (00-59). 47
%m DATE
TIMESTAMP
 The month as a decimal number (01-12). 01
%n All A newline character.
%P TIMESTAMP
 When formatting, this is either am or pm.
This cannot be used with parsing. Instead, use %p.
pm
%p TIMESTAMP
 When formatting, this is either AM or PM.
When parsing, this can be used with am, pm, AM, or PM.
 PM
%Q DATE
TIMESTAMP
 The quarter as a decimal number (1-4). 1
%R TIMESTAMP
 The time in the format %H:%M. 21:47
%S TIMESTAMP
 The second as a decimal number (00-60). 00
%s TIMESTAMP
 The number of seconds since 1970-01-01 00:00:00. Always overrides all other format elements, independent of where %s appears in the string. If multiple %s elements appear, then the last one takes precedence. 1611179220
%T TIMESTAMP
 The time in the format %H:%M:%S. 21:47:00
%t All A tab character.
%U DATE
TIMESTAMP
 The week number of the year (Sunday as the first day of the week) as a decimal number (00-53). 03
%u DATE
TIMESTAMP
 The weekday (Monday as the first day of the week) as a decimal number (1-7). 3
%V DATE
TIMESTAMP
 The ISO 8601 week number of the year (Monday as the first day of the week) as a decimal number (01-53). If the week containing January 1 has four or more days in the new year, then it is week 1; otherwise it is week 53 of the previous year, and the next week is week 1. 03
%W DATE
TIMESTAMP
 The week number of the year (Monday as the first day of the week) as a decimal number (00-53). 03
%w DATE
TIMESTAMP
 The weekday (Sunday as the first day of the week) as a decimal number (0-6). 3
%X TIMESTAMP
 The time representation in HH:MM:SS format. 21:47:00
%x DATE
TIMESTAMP
 The date representation in MM/DD/YY format. 01/20/21
%Y DATE
TIMESTAMP
 The year with century as a decimal number. 2021
%y DATE
TIMESTAMP
 The year without century as a decimal number (00-99), with an optional leading zero. Can be mixed with %C. If %C is not specified, years 00-68 are 2000s, while years 69-99 are 1900s. 21
%Z TIMESTAMP
 The time zone name. UTC-5
%z TIMESTAMP
 The offset from the Prime Meridian in the format +HHMM or -HHMM as appropriate, with positive values representing locations east of Greenwich. -0500
%% All A single % character. %
%Ez TIMESTAMP
 RFC 3339-compatible numeric time zone (+HH:MM or -HH:MM). -05:00
%E<number>S TIMESTAMP
 Seconds with <number> digits of fractional precision. 00.000 for %E3S
%E*S TIMESTAMP
 Seconds with full fractional precision (a literal '*'). 00.123456789
%E4Y DATE
TIMESTAMP
 Four-character years (0001 ... 9999). Note that %Y produces as many characters as it takes to fully render the year. 2021

Examples:

SELECT FORMAT_DATE("%b-%d-%Y", DATE "2008-12-25") AS formatted;

/*-------------*
 | formatted   |
 +-------------+
 | Dec-25-2008 |
 *-------------*/

SELECT FORMAT_TIMESTAMP("%b %Y %Ez", TIMESTAMP "2008-12-25 15:30:00+00")
  AS formatted;

/*-----------------*
 | formatted       |
 +-----------------+
 | Dec 2008 +00:00 |
 *-----------------*/

SELECT PARSE_DATE("%Y%m%d", "20081225") AS parsed;

/*------------*
 | parsed     |
 +------------+
 | 2008-12-25 |
 *------------*/

SELECT PARSE_TIMESTAMP("%c", "Thu Dec 25 07:30:00 2008") AS parsed;

-- Display of results may differ, depending upon the environment and
-- time zone where this query was executed.
/*------------------------*
 | parsed                 |
 +------------------------+
 | 2008-12-25T15:30:00Z   |
 *------------------------*/