Datetime format strings - AWS Clean Rooms

Datetime format strings

The following datetime format strings apply to functions such as TO_CHAR. These strings can contain datetime separators (such as '-', '/', or ':') and the following "dateparts" and "timeparts".

For examples of formatting dates as strings, see TO_CHAR.

Datepart or timepart Meaning
BC or B.C., AD or A.D., b.c. or bc, ad or a.d. Upper and lowercase era indicators
CC Two-digit century number
YYYY, YYY, YY, Y 4-digit, 3-digit, 2-digit, 1-digit year number
Y,YYY 4-digit year number with comma
IYYY, IYY, IY, I 4-digit, 3-digit, 2-digit, 1-digit International Organization for Standardization (ISO) year number
Q Quarter number (1 to 4)
MONTH, Month, month Month name (uppercase, mixed-case, lowercase, blank-padded to 9 characters)
MON, Mon, mon Abbreviated month name (uppercase, mixed-case, lowercase, blank-padded to 3 characters)
MM Month number (01-12)
RM, rm Month number in Roman numerals (I–XII, with I being January, uppercase or lowercase)
W Week of month (1–5; the first week starts on the first day of the month.)
WW Week number of year (1–53; the first week starts on the first day of the year.)
IW ISO week number of year (the first Thursday of the new year is in week 1.)
DAY, Day, day Day name (uppercase, mixed-case, lowercase, blank-padded to 9 characters)
DY, Dy, dy Abbreviated day name (uppercase, mixed-case, lowercase, blank-padded to 3 characters)
DDD Day of year (001–366)
IDDD Day of ISO 8601 week-numbering year (001-371; day 1 of the year is Monday of the first ISO week)
DD Day of month as a number (01–31)
D Day of week (1–7; Sunday is 1)
Note

The D datepart behaves differently from the day of week (DOW) datepart used for the datetime functions DATE_PART and EXTRACT. DOW is based on integers 0–6, where Sunday is 0. For more information, see Date parts for date or timestamp functions.

ID ISO 8601 day of the week, Monday (1) to Sunday (7)
J Julian day (days since January 1, 4712 BC)
HH24 Hour (24-hour clock, 00–23)
HH or HH12 Hour (12-hour clock, 01–12)
MI Minutes (00–59)
SS Seconds (00–59)
MS Milliseconds (.000)
US Microseconds (.000000)
AM or PM, A.M. or P.M., a.m. or p.m., am or pm Upper and lowercase meridian indicators (for 12-hour clock)
TZ, tz Upper and lowercase time zone abbreviation; valid for TIMESTAMPTZ only
OF Offset from UTC; valid for TIMESTAMPTZ only
Note

You must surround datetime separators (such as '-', '/' or ':') with single quotation marks, but you must surround the "dateparts" and "timeparts" listed in the preceding table with double quotation marks.