RetroSearch Browse

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://cloud.google.com/spanner/docs/reference/standard-sql/format-elements below:

Format elements in GoogleSQL | Spanner

Format elements in GoogleSQL

Stay organized with collections Save and categorize content based on your preferences.

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:

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

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-08-07 UTC.

[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-07 UTC."],[],[]]

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4