Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

Datetime Format Strings

You can find a reference for datetime format strings following.

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

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 9 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 9 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)


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 Dateparts for Date or Time Stamp 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


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.

The following example shows formatting for seconds, milliseconds, and microseconds.

select sysdate, to_char(sysdate, 'HH24:MI:SS') as seconds, to_char(sysdate, 'HH24:MI:SS.MS') as milliseconds, to_char(sysdate, 'HH24:MI:SS:US') as microseconds; timestamp | seconds | milliseconds | microseconds --------------------+----------+--------------+---------------- 2015-04-10 18:45:09 | 18:45:09 | 18:45:09.325 | 18:45:09:325143