Format elements

Format elements for date and time parts

Many Google Standard SQL 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
DATETIME
TIMESTAMP
 The full weekday name. Wednesday
%a DATE
DATETIME
TIMESTAMP
 The abbreviated weekday name. Wed
%B DATE
DATETIME
TIMESTAMP
 The full month name. January
%b DATE
DATETIME
TIMESTAMP
 The abbreviated month name. Jan
%C DATE
DATETIME
TIMESTAMP
 The century (a year divided by 100 and truncated to an integer) as a decimal number (00-99). 20
%c DATETIME
TIMESTAMP
 The date and time representation. Wed Jan 20 21:47:00 2021
%D DATE
DATETIME
TIMESTAMP
 The date in the format %m/%d/%y. 01/20/21
%d DATE
DATETIME
TIMESTAMP
 The day of the month as a decimal number (01-31). 20
%e DATE
DATETIME
TIMESTAMP
 The day of month as a decimal number (1-31); single digits are preceded by a space. 20
%F DATE
DATETIME
TIMESTAMP
 The date in the format %Y-%m-%d. 2021-01-20
%G DATE
DATETIME
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
DATETIME
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 TIME
DATETIME
TIMESTAMP
 The hour (24-hour clock) as a decimal number (00-23). 21
%h DATE
DATETIME
TIMESTAMP
 The abbreviated month name. Jan
%I TIME
DATETIME
TIMESTAMP
 The hour (12-hour clock) as a decimal number (01-12). 09
%J DATE
DATETIME
TIMESTAMP
 The ISO 8601 1-based day of the year (1-364 or 1-371 days). If ISO year is not set, this is ignored. 364
%j DATE
DATETIME
TIMESTAMP
 The day of the year as a decimal number (001-366). 020
%k TIME
DATETIME
TIMESTAMP
 The hour (24-hour clock) as a decimal number (0-23); single digits are preceded by a space. 21
%l TIME
DATETIME
TIMESTAMP
 The hour (12-hour clock) as a decimal number (1-12); single digits are preceded by a space. 9
%M TIME
DATETIME
TIMESTAMP
 The minute as a decimal number (00-59). 47
%m DATE
DATETIME
TIMESTAMP
 The month as a decimal number (01-12). 01
%n All A newline character.
%P TIME
DATETIME
TIMESTAMP
 Either am or pm. pm
%p TIME
DATETIME
TIMESTAMP
 Either AM or PM. PM
%Q DATE
DATETIME
TIMESTAMP
 The quarter as a decimal number (1-4). 1
%R TIME
DATETIME
TIMESTAMP
 The time in the format %H:%M. 21:47
%S TIME
DATETIME
TIMESTAMP
 The second as a decimal number (00-60). 00
%s TIME
DATETIME
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 TIME
DATETIME
TIMESTAMP
 The time in the format %H:%M:%S. 21:47:00
%t All A tab character.
%U DATE
DATETIME
TIMESTAMP
 The week number of the year (Sunday as the first day of the week) as a decimal number (00-53). 03
%u DATE
DATETIME
TIMESTAMP
 The weekday (Monday as the first day of the week) as a decimal number (1-7). 3
%V DATE
DATETIME
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
DATETIME
TIMESTAMP
 The week number of the year (Monday as the first day of the week) as a decimal number (00-53). 03
%w DATE
DATETIME
TIMESTAMP
 The weekday (Sunday as the first day of the week) as a decimal number (0-6). 3
%X TIME
DATETIME
TIMESTAMP
 The time representation in HH:MM:SS format. 21:47:00
%x DATE
DATETIME
TIMESTAMP
 The date representation in MM/DD/YY format. 01/20/21
%Y DATE
DATETIME
TIMESTAMP
 The year with century as a decimal number. 2021
%y DATE
DATETIME
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 TIME
DATETIME
TIMESTAMP
 Seconds with <number> digits of fractional precision. 00.000 for %E3S
%E*S TIME
DATETIME
TIMESTAMP
 Seconds with full fractional precision (a literal '*'). 00.123456
%E4Y DATE
DATETIME
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_DATETIME("%c", DATETIME "2008-12-25 15:30:00")
  AS formatted;

+--------------------------+
| formatted                |
+--------------------------+
| Thu Dec 25 15:30:00 2008 |
+--------------------------+

SELECT FORMAT_TIME("%R", TIME "15:30:00") as formatted_time;

+----------------+
| formatted_time |
+----------------+
| 15:30          |
+----------------+

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_DATETIME('%Y-%m-%d %H:%M:%S', '1998-10-18 13:45:55') AS datetime;

+---------------------+
| datetime            |
+---------------------+
| 1998-10-18T13:45:55 |
+---------------------+

SELECT PARSE_TIME('%I:%M:%S %p', '2:23:38 pm') AS parsed_time

+-------------+
| parsed_time |
+-------------+
| 14:23:38    |
+-------------+

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-25 07:30:00 UTC |
+-------------------------+