Template patterns for date/time formatting

In an output template string (for TO_CHAR), certain patterns are recognized and replaced with appropriately formatted data from the value to format.

In an output template string (for TO_CHAR), certain patterns are recognized and replaced with appropriately formatted data from the value to format. Any text that is not a template pattern is copied verbatim. Similarly, in an input template string (for anything other than TO_CHAR), template patterns identify the parts of the input data string to look at and the values to find there.

Pattern Description
HH Hour of day (00-23)
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 A.M. PM P.M. Meridian indicator (uppercase)
am a.m. pm p.m. Meridian indicator (lowercase)
Y YYY Year (4 and more digits) with comma
YYYY Year (4 and more digits)
YYY Last 3 digits of year
YY Last 2 digits of year
Y Last digit of year
IYYY ISO year (4 and more digits)
IYY Last 3 digits of ISO year
IY Last 2 digits of ISO year
I Last digits of ISO year
BC B.C. AD A.D. Era indicator (uppercase)
bc b.c. ad a.d. Era indicator (lowercase)
MONTH Full uppercase month name (blank-padded to 9 chars)
Month Full mixed-case month name (blank-padded to 9 chars)
month Full lowercase month name (blank-padded to 9 chars)
MON Abbreviated uppercase month name (3 chars)
Mon Abbreviated mixed-case month name (3 chars)
mon Abbreviated lowercase month name (3 chars)
MM Month number (01-12)
DAY Full uppercase day name (blank-padded to 9 chars)
Day Full mixed-case day name (blank-padded to 9 chars)
day full lowercase day name (blank-padded to 9 chars)
DY Abbreviated uppercase day name (3 chars)
Dy Abbreviated mixed-case day name (3 chars)
dy Abbreviated lowercase day name (3 chars)
DDD Day of year (001-366)
DD

Day of month (01-31) for TIMESTAMP

D Day of week (1-7; Sunday is 1)
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.)
CC Century (2 digits)
J Julian Day (days since January 1, 4712 BC)
Q Quarter
RM Month in Roman numerals (I-XII; I=January) (uppercase)
rm Month in Roman numerals (i-xii; i=January) (lowercase)
TZ Time-zone name (uppercase)
tz Time-zone name (lowercase)

The TO_DATE, TO_TIMESTAMP, and TO_TIMESTAMP_TZ functions do not validate the range of date or hour components during conversion. While these functions accept and convert input values, supplying out-of-range values (for example, time '25:10' has out-of-range hour) may result in shifted or unexpected date or timestamp outputs rather than raising an error.

Example: The 'DD' in the input is invalid. The 'TO_DATE' returns an error.

=> SELECT TO_DATE('TwoOCT25', 'DDMonYY');
   ERROR 3677:  Invalid input for dd: "twoOct25"

Example: If the 'DD' value is out of range, such as 32, the function does not raise an error. Instead, it shifts the date forward by one day, converting '32OCT24' into November 1, 2024.

=> SELECT TO_DATE ('32OCT24', 'DDMONYY');
  to_date
------------
 2024-11-01
(1 row)

Example: If the 'HH' value is out of range, such as 25, the function does not raise an error. Instead, it interprets the value as 24 hours plus 1 hour, resulting in a date shift. Thus, converting '29OCT2425:32:25' into '2024-10-30 01:32:25'.

=> SELECT TO_TIMESTAMP ('29OCT2425:32:25','DDMONYYHH:MI:SS');
    TO_TIMESTAMP
---------------------
 2024-10-30 01:32:25
(1 row)

Template pattern modifiers

Certain modifiers can be applied to any date/time template pattern to alter its behavior. For example, FMMonth is the Month pattern with the FM modifier.

Modifier Description
AM Time is before 12:00
AT Ignored
JULIAN, JD, J Next field is Julian Day
FM prefix

Fill mode (suppress padding blanks and zeros)

For example: FMMonth

Note: The FM modifier suppresses leading zeros and trailing blanks that would otherwise be added to make the output of a pattern fixed width.

FX prefix

Fixed format global option

For example: FX Month DD Day

ON Ignored
PM Time is on or after 12:00
T Next field is time
TH suffix

Uppercase ordinal number suffix

For example: DDTH

th suffix

Lowercase ordinal number suffix

For example: DDth

TM prefix Translation mode (print localized day and month names based on lc_messages). For example: TMMonth

Examples

Use TO_TIMESTAMP to convert an expression using the pattern 'YYY MON':

=> SELECT TO_TIMESTAMP('2017 JUN', 'YYYY MON');
    TO_TIMESTAMP
---------------------
 2017-06-01 00:00:00
(1 row)

Use TO_DATE to convert an expression using the pattern 'YYY-MMDD':

=> SELECT TO_DATE('2017-1231', 'YYYY-MMDD');
  TO_DATE
------------
 2017-12-31
(1 row)