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 stringto describe the format of parsed or formatted values. A format string representsthe textual form of date and time and contains separate format elements that areapplied left-to-right.
These functions use format strings:
Format strings generally support the following elements:
| Format element | Type | Description | Example |
|---|---|---|---|
%A | DATETIMESTAMP | The full weekday name (English). | Wednesday |
%a | DATETIMESTAMP | The abbreviated weekday name (English). | Wed |
%B | DATETIMESTAMP | The full month name (English). | January |
%b | DATETIMESTAMP | The abbreviated month name (English). | Jan |
%C | DATETIMESTAMP | 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 | DATETIMESTAMP | The date in the format %m/%d/%y. | 01/20/21 |
%d | DATETIMESTAMP | The day of the month as a decimal number (01-31). | 20 |
%e | DATETIMESTAMP | The day of month as a decimal number (1-31); single digits are preceded by a space. | 20 |
%F | DATETIMESTAMP | The date in the format %Y-%m-%d. | 2021-01-20 |
%G | DATETIMESTAMP | TheISO 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 | DATETIMESTAMP | TheISO 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 | DATETIMESTAMP | The abbreviated month name (English). | Jan |
%I | TIMESTAMP | The hour (12-hour clock) as a decimal number (01-12). | 09 |
%j | DATETIMESTAMP | 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 | DATETIMESTAMP | 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 | DATETIMESTAMP | 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 | DATETIMESTAMP | The week number of the year (Sunday as the first day of the week) as a decimal number (00-53). | 03 |
%u | DATETIMESTAMP | The weekday (Monday as the first day of the week) as a decimal number (1-7). | 3 |
%V | DATETIMESTAMP | TheISO 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 | DATETIMESTAMP | The week number of the year (Monday as the first day of the week) as a decimal number (00-53). | 03 |
%w | DATETIMESTAMP | 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 | DATETIMESTAMP | The date representation in MM/DD/YY format. | 01/20/21 |
%Y | DATETIMESTAMP | The year with century as a decimal number. | 2021 |
%y | DATETIMESTAMP | 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 | DATETIMESTAMP | 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-25T15:30:00Z | +------------------------*/Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
Last updated 2025-12-15 UTC.