Valid Datetime Format Strings

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.

%YYear with centurySELECT FORMAT_DATE('%Y', DATE '2019-08-17')'2019'
%yYear without century (00 - 99)SELECT FORMAT_DATE('%y', DATE '2019-08-17')'19'
%mMonth (01 - 12)SELECT FORMAT_DATE('%Y/%m', DATE '2019-08-17')'2019/08'
%dDay of the month (01 - 31)SELECT FORMAT_DATE('%Y/%m/%d', DATE '2019-08-17')'2019/08/17'
%aAbbreviated name of the day of the weekSELECT FORMAT_DATE('%Y/%m/%d, (%a)', DATE '2019-08-17')'2019/08/17, (Sat)'
%AFull name of the day of the weekSELECT FORMAT_DATE('%Y/%m/%d, (%A)', DATE '2019-08-17')'2019/08/17, (Saturday)'
%bAbbreviated month nameSELECT FORMAT_DATE('%d %b, %Y', DATE '2019-08-17')'17 Aug, 2019'
%BFull month nameSELECT FORMAT_DATE('%d %B, %Y', DATE '2019-08-17')'17 August, 2019'
%FEquivalent to %Y-%m-%dSELECT FORMAT_DATE('%F', DATE '2019-08-17')'2019-08-17'
%DEquivalent to %m/%d/%ySELECT FORMAT_DATE('%D', DATE '2019-08-17')'08/17/19'
%HHour using a 24-hour clock (00 - 23)SELECT FORMAT_TIME('%H', TIME '21:10:11')'21'
%IHour using a 12-hour clock (01 - 12)SELECT FORMAT_TIME('%I', TIME '21:10:11')'09'
%MMinute (00 - 59)SELECT FORMAT_TIME('%H:%M', TIME '21:10:11')'21:10'
%SSeconds (00 - 60)SELECT FORMAT_TIME('%H:%M:%S', TIME '21:10:11')'21:10:11'
%TEquivalent to %H:%M:%SSELECT FORMAT_TIME('%T', TIME '21:10:11')'21:10:11'
%sSeconds 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:

%EzNumeric 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#SSeconds with # digits of fractional precisionSELECT FORMAT_TIME('%H:%M:%E3S', TIME '10:10:11.314567')'10:10:11.314'
%E*SSeconds with full fractional precision (literal '*')SELECT FORMAT_DATE('%H:%M:%E*S', TIME '10:10:11.314567')'10:10:11.314567'