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 can't 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's week 1; otherwise it's 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 isn't 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:

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

SELECTFORMAT_TIMESTAMP("%b %Y %Ez",TIMESTAMP"2008-12-25 15:30:00+00")
ASformatted;
/*-----------------*
 | formatted |
 +-----------------+
 | Dec 2008 +00:00 |
 *-----------------*/

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

SELECTPARSE_TIMESTAMP("%c","Thu Dec 25 07:30:00 2008")ASparsed;
-- Display of results may differ, depending upon the environment and
-- time zone where this query was executed.
/*------------------------*
 | parsed |
 +------------------------+
 | 2008年12月25日T15:30:00Z |
 *------------------------*/

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025年10月13日 UTC.