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 '
:') 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)|
|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.
|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)|
|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|
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