Formats for Datetime Values

The following formats (datetime masks) are supported in arguments to the following functions:
Format Description
HH, HH12 Hour of day (01-12)
HH24 Hour of day (00-23)
MI Minute (00-59)
SS Second (00-59)
MS Millisecond (000-999)
US Microsecond (000000-999999)
SSSS Seconds past midnight (0-86399)
AM, am, PM, pm, A.M., a.m., P.M., p.m. Meridiem indicator (with and without periods)
Y,YYY 4-digit year with comma
YYYY, YYY, YY, Y 4-digit year, last 3, last 2, last 1
IYYY, IYY, IY, I ISO 8601 week-numbering year (4 digits, last 3, last 2 last 1)
BC, bc, AD, ad, B.C., b.c., A.D., a.d. Era indicator (with and without periods)
MONTH, Month, month Uppercase, capitalized, or lowercase month name (blank-padded to 9 characters)
MON, Mon, mon Abbreviated uppercase, capitalized, or lowercase month name (3 characters in English)
MM Month number (01-12)
DAY, Day, day Uppercase, capitalized, or lowercase day name (blank-padded to 9 characters)
DY, Dy, dy Abbreviated uppercase, capitalized, or lowercase day name (3 characters in English)
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 (01-31)
D Day of the week, Sunday (1) to Saturday (7)
FM Fill mode prefix (a pattern modifier that suppresses trailing zeroes and padding blanks)
FX This prefix must be the first item in the format string. Normally, TO_TIMESTAMP and TO_DATE functions ignore multiple blank spaces. If you specify the FX prefix, these functions do not ignore blank spaces. For example: FX Month DD Day. This prefix is not supported with the TO_CHAR function.
ID ISO 8601 day of the week, Monday (1) to Sunday (7)
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 Week number of ISO 8601 week-numbering year (01-53; the first Thursday of the year is in week 1)
CC Century (2 digits) (the twenty-first century starts on 2001-01-01). The CC pattern is ignored when the year is specified.
J Julian Day (integer days since November 24, 4714 BC at midnight UTC) Not supported by TO_CHAR.
Q Quarter (ignored by TO_DATE and TO_TIMESTAMP)
RM, rm Month in uppercase or lowercase Roman numerals (I-XII and I=January or i-xii and i=January)

Not supported by TO_CHAR.

TH, th Ordinal number suffix (a pattern modifier). For example: DDth or DDTH
TZ, tz Uppercase or lowercase time-zone name (the long form of the name. For example: PST8PDT is returned, not PST.
OF Time-zone offset

Not supported by TO_CHAR.

To convert Julian dates to the yyyy-mm-dd format, store the Julian dates as VARCHAR with a J preceeding the value. For example:
premdb=# create table yb100 (julian varchar(10));

premdb=# insert into yb100 values ('J2456783');

premdb=# SELECT julian::DATE from yb100;
(1 row)
Using the same table, Julian dates can be converted to the yyyy-mm-dd format using the TO_DATE function:
premdb=# SELECT TO_DATE(2456783::text, 'J');
(1 row)

Separators in Date Formats

The basic date formats work with or without separator characters and with uppercase and lowercase letters; however, the literal date values and the corresponding date masks must match. Do not specify separators in the mask that do not exist in the input string, and do not use additional separator characters, such as extra spaces or hyphens, between format elements unless the input string also contains those characters.

For example, the following date masks are valid for a date such as 30 May 2016:
dd mon yyyy
However, the following masks will fail for the same input string:
dd  mon  yyyy

Year Formats

Do not use a four-digit year in the mask if the input string contains fewer digits. For example:
premdb=# SELECT TO_DATE('30 May 16 ', 'dd mon yyyy') from sys.const;
(1 row)

The correct mask in this case would be dd mon yy.

Conversely, if the input value contains a four-digit year, the mask may specify YYYY, YYY, YY, or Y. For example:
premdb=# SELECT TO_DATE('30-May-2016', 'DD-Mon-Y') from sys.const;
(1 row)

Handling of US Format (Microseconds)

The TO_TIMESTAMP function ignores any leading zeroes that appear in the format for microseconds. For example:
premdb=# select to_timestamp('20180228 11 59 59 000999 AM', 'YYYYMMDD HH12 MI SS US AM') from sys.const;
 2018-02-28 11:59:59.999-08
(1 row)