This section describes some of the important formats supported to convert a date, time, datetime, or timestamp value to string. The format specification is the standard strftime with CCTZ extensions.
Note on Out-of-Range Handling: Date and time values that are out-of-range are normalized (e.g., Nov 32 -> Dec 2) so that all date and time objects represent valid values.
| FORMAT | Meaning | Query | Result |
|---|---|---|---|
%Y | Year with century | SELECT FORMAT_DATE('%Y', DATE '2019-08-17') | '2019' |
%y | Year without century (00 - 99) | SELECT FORMAT_DATE('%y', DATE '2019-08-17') | '19' |
%m | Month (01 - 12) | SELECT FORMAT_DATE('%Y/%m', DATE '2019-08-17') | '2019/08' |
%d | Day of the month (01 - 31) | SELECT FORMAT_DATE('%Y/%m/%d', DATE '2019-08-17') | '2019/08/17' |
%a | Abbreviated name of the day of the week | SELECT FORMAT_DATE('%Y/%m/%d, (%a)', DATE '2019-08-17') | '2019/08/17, (Sat)' |
%A | Full name of the day of the week | SELECT FORMAT_DATE('%Y/%m/%d, (%A)', DATE '2019-08-17') | '2019/08/17, (Saturday)' |
%b | Abbreviated month name | SELECT FORMAT_DATE('%d %b, %Y', DATE '2019-08-17') | '17 Aug, 2019' |
%B | Full month name | SELECT FORMAT_DATE('%d %B, %Y', DATE '2019-08-17') | '17 August, 2019' |
%F | Equivalent to %Y-%m-%d | SELECT FORMAT_DATE('%F', DATE '2019-08-17') | '2019-08-17' |
%D | Equivalent to %m/%d/%y | SELECT FORMAT_DATE('%D', DATE '2019-08-17') | '08/17/19' |
%H | Hour using a 24-hour clock (00 - 23) | SELECT FORMAT_TIME('%H', TIME '21:10:11') | '21' |
%I | Hour using a 12-hour clock (01 - 12) | SELECT FORMAT_TIME('%I', TIME '21:10:11') | '09' |
%M | Minute (00 - 59) | SELECT FORMAT_TIME('%H:%M', TIME '21:10:11') | '21:10' |
%S | Seconds (00 - 60) | SELECT FORMAT_TIME('%H:%M:%S', TIME '21:10:11') | '21:10:11' |
%T | Equivalent to %H:%M:%S | SELECT FORMAT_TIME('%T', TIME '21:10:11') | '21:10:11' |
%s | Seconds since the Epoch, 1970-01-01 00:00:00 (UTC) | SELECT FORMAT_TIME('%s', TIME '21:10:11') | '76211' |
We also support formatting options with the following extentions:
| FORMAT | Meaning | Query | Result |
|---|---|---|---|
%Ez | Numeric UTC offset (+hh:mm or -hh:mm) | SELECT FORMAT_DATETIME('%F, %T %Ez', TIMESTAMP '2019-08-17T10:10:11+7') | '2019-08-17, 03:10:11 +00:00' |
%E#S | Seconds with # digits of fractional precision | SELECT FORMAT_TIME('%H:%M:%E3S', TIME '10:10:11.314567') | '10:10:11.314' |
%E*S | Seconds with full fractional precision (literal '*') | SELECT FORMAT_DATE('%H:%M:%E*S', TIME '10:10:11.314567') | '10:10:11.314567' |