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_DATE
FORMAT_DATETIME
FORMAT_TIME
FORMAT_TIMESTAMP
PARSE_DATE
PARSE_DATETIME
PARSE_TIME
PARSE_TIMESTAMP
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 |
+-------------------------+