Data Type Formatting Functions v12
The Advanced Server formatting functions described in the following table provide a powerful set of tools for converting various data types (date/time, integer, floating point, numeric) to formatted strings and for converting from formatted strings to specific data types. These functions all follow a common calling convention: the first argument is the value to be formatted and the second argument is a string template that defines the output or input format.
Function | Return Type | Description | Example | Result |
---|---|---|---|---|
TO_CHAR(DATE [, format ]) | VARCHAR2 | Convert a date/time to a string with output, format . If omitted default format is DD-MON-YY. | TO_CHAR(SYSDATE, 'MM/DD/YYYY HH12:MI:SS AM') | 07/25/2007 09:43:02 AM |
TO_CHAR(TIMESTAMP [, format ]) | VARCHAR2 | Convert a timestamp to a string with output, format . If omitted default format is DD-MON-YY. | TO_CHAR (CURRENT_TIMESTAMP, 'MM/DD/YYYY HH12:MI:SS AM') | 08/13/2015 08:55:22 PM |
TO_CHAR(INTEGER [, format ]) | VARCHAR2 | Convert an integer to a string with output, format | TO_CHAR(2412, '999,999S') | 2,412+ |
TO_CHAR(NUMBER [, format ]) | VARCHAR2 | Convert a decimal number to a string with output, format | TO_CHAR(10125.35, '999,999.99') | 10,125.35 |
TO_CHAR(DOUBLE PRECISION, format) | VARCHAR2 | Convert a floating-point number to a string with output, format | TO_CHAR (CAST(123.5282 AS REAL), '999.99') | 123.53 |
TO_DATE(string [, format ]) | TIMESTAMP | Convert a date or timestamp formatted string to a TIMESTAMP data type | TO_DATE('2007-07-04 13:39:10', 'YYYY-MM-DD HH24:MI:SS') TO_DATE('2007-07-04','YYYY-MM-DD') | 04-JUL-07 13:39:10 04-JUL-07 00:00:00 |
TO_NUMBER(string [, format ]) | NUMBER | Convert a number formatted string to a NUMBER data type | TO_NUMBER('2,412-', '999,999S') | -2412 |
TO_TIMESTAMP(string, format) | TIMESTAMPTZ | Convert a timestamp formatted string to a TIMESTAMPTZ data type | TO_TIMESTAMP('05 Dec 2000 08:30:25 pm', 'DD Mon YYYY hh12:mi:ss pm') | 05-DEC-00 20:30:25 +05:30 |
TO_CHAR, TO_DATE, and TO_TIMESTAMP
In an output template string (for TO_CHAR
), there are certain patterns that are recognized and replaced with appropriately-formatted data from the value to be formatted. Any text that is not a template pattern is simply copied verbatim. Similarly, in an input template string (for anything but TO_CHAR
), template patterns identify the parts of the input data string to be looked at and the values to be found there.
If you do not specify a date, month, or year when calling TO_TIMESTAMP
or TO_DATE
, then by default the output format considers the first date of a current month or current year respectively. In the following example, date, month, and year is not specified in the input string; TO_TIMESTAMP
and TO_DATE
returns a default value of the first date of a current month and current year.
The following table shows the template patterns available for formatting date values using the TO_CHAR
, TO_DATE
, and TO_TIMESTAMP
functions.
Pattern | Description |
---|---|
HH | Hour of day (01-12) |
HH12 | Hour of day (01-12) |
HH24 | Hour of day (00-23) |
MI | Minute (00-59) |
SS | Second (00-59) |
SSSSS | Seconds past midnight (0-86399) |
FFn | Fractional seconds where n is an optional integer from 1 to 9 for the number of digits to return. If omitted, the default is 6. |
AM or A.M. or PM or P.M. | Meridian indicator (uppercase) |
am or a.m. or pm or p.m. | Meridian indicator (lowercase) |
Y,YYY | Year (4 and more digits) with comma |
YEAR | Year (spelled out) |
SYEAR | Year (spelled out) (BC dates prefixed by a minus sign) |
YYYY | Year (4 and more digits) |
SYYYY | Year (4 and more digits) (BC dates prefixed by a minus sign) |
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 1 digit of ISO year |
BC or B.C. or AD or A.D. | Era indicator (uppercase) |
bc or b.c. or ad or a.d. | Era indicator (lowercase) |
MONTH | Full uppercase month name |
Month | Full mixed-case month name |
month | Full lowercase month name |
MON | Abbreviated uppercase month name (3 chars in English, localized lengths vary) |
Mon | Abbreviated mixed-case month name (3 chars in English, localized lengths vary) |
mon | Abbreviated lowercase month name (3 chars in English, localized lengths vary) |
MM | Month number (01-12) |
DAY | Full uppercase day name |
Day | Full mixed-case day name |
day | Full lowercase day name |
DY | Abbreviated uppercase day name (3 chars in English, localized lengths vary) |
Dy | Abbreviated mixed-case day name (3 chars in English, localized lengths vary) |
dy | Abbreviated lowercase day name (3 chars in English, localized lengths vary) |
DDD | Day of year (001-366) |
DD | Day of month (01-31) |
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); the 21st century starts on 2001-01-01 |
SCC | Same as CC except BC dates are prefixed by a minus sign |
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) |
RR | First 2 digits of the year when given only the last 2 digits of the year. Result is based upon an algorithm using the current year and the given 2-digit year. The first 2 digits of the given 2-digit year will be the same as the first 2 digits of the current year with the following exceptions: If the given 2-digit year is <50 and the last 2 digits of the current year is >= 50, then the first 2 digits for the given year is 1 greater than the first 2 digits of the current year. If the given 2-digit year is >= 50 and the last 2 digits of the current year is <50, then the first 2 digits for the given year is 1 less than the first 2 digits of the current year. |
RRRR | Only affects TO_DATE function. Allows specification of 2-digit or 4-digit year. If 2-digit year given, then returns first 2 digits of year like RR format. If 4-digit year given, returns the given 4-digit year. |
Date and Time Modifiers
Certain modifiers may be applied to any template pattern to alter its behavior. For example, FMMonth
is the Month
pattern with the FM
modifier. The following table shows the modifier patterns for date/time formatting.
Modifier | Description | Example |
---|---|---|
FM prefix | Fill mode (suppress padding blanks and zeros) | FMMonth |
TH suffix | Uppercase ordinal number suffix | DDTH |
th suffix | Lowercase ordinal number suffix | DDth |
FX prefix | Fixed format global option (see usage notes) | FX Month DD Day |
SP suffix | Spell mode | DDSP |
Usage notes for date/time formatting:
FM
suppresses leading zeroes and trailing blanks that would otherwise be added to make the output of a pattern fixed-width.TO_TIMESTAMP
andTO_DATE
skip multiple blank spaces in the input string if theFX
option is not used.FX
must be specified as the first item in the template. For example:Ordinary text is allowed in
TO_CHAR
templates and will be output literally.In conversions from string to
timestamp
ordate
, theCC
field is ignored if there is aYYY
,YYYY
orY,YYY
field. IfCC
is used withYY
orY
then the year is computed as(CC-1)*100+YY
.
The following table shows some examples of the use of the TO_CHAR
and TO_DATE
functions:
Expression | Result |
---|---|
TO_CHAR(CURRENT_TIMESTAMP, 'Day, DD HH12:MI:SS') | 'Tuesday , 06 05:39:18' |
TO_CHAR(CURRENT_TIMESTAMP, 'FM-Day, FMDD HH12:MI:SS') | 'Tuesday, 6 05:39:18' |
TO_CHAR(-0.1, '99.99') | ' -.10' |
TO_CHAR(-0.1, 'FM9.99') | '-.1' |
TO_CHAR(0.1, '0.9') | ' 0.1' |
TO_CHAR(12, '9990999.9') | ' 0012.0' |
TO_CHAR(12, 'FM9990999.9') | '0012.' |
TO_CHAR(485, '999') | ' 485' |
TO_CHAR(-485, '999') | '-485' |
TO_CHAR(1485, '9,999') | ' 1,485' |
TO_CHAR(1485, '9G999') | ' 1,485' |
TO_CHAR(148.5, '999.999') | ' 148.500' |
TO_CHAR(148.5, 'FM999.999') | '148.5' |
TO_CHAR(148.5, 'FM999.990') | '148.500' |
TO_CHAR(148.5, '999D999') | ' 148.500' |
TO_CHAR(3148.5, '9G999D999') | ' 3,148.500' |
TO_CHAR(-485, '999S') | '485-' |
TO_CHAR(-485, '999MI') | '485-' |
TO_CHAR(485, '999MI') | '485 ' |
TO_CHAR(485, 'FM999MI') | '485' |
TO_CHAR(-485, '999PR') | '<485>' |
TO_CHAR(485, 'L999') | '$ 485' |
TO_CHAR(485, 'RN') | ' CDLXXXV' |
TO_CHAR(485, 'FMRN') | 'CDLXXXV' |
TO_CHAR(5.2, 'FMRN') | 'V' |
TO_CHAR(12, '99V999') | ' 12000' |
TO_CHAR(12.4, '99V999') | ' 12400' |
TO_CHAR(12.45, '99V9') | ' 125' |
IMMUTABLE TO_CHAR(TIMESTAMP, format) Function
There are certain cases of the TO_CHAR
function that can result in usage of an IMMUTABLE
form of the function. Basically, a function is IMMUTABLE
if the function does not modify the database, and the function returns the same, consistent value dependent upon only its input parameters. That is, the settings of configuration parameters, the locale, the content of the database, etc. do not affect the results returned by the function.
For more information about function volatility categories VOLATILE
, STABLE
, and IMMUTABLE
, see the PostgreSQL Core documentation at:
https://www.postgresql.org/docs/12/static/xfunc-volatility.html
A particular advantage of an IMMUTABLE
function is that it can be used in the CREATE INDEX
command to create an index based on that function.
In order for the TO_CHAR
function to use the IMMUTABLE
form the following conditions must be satisfied:
- The first parameter of the
TO_CHAR
function must be of data typeTIMESTAMP
. - The format specified in the second parameter of the
TO_CHAR
function must not affect the return value of the function based on factors such as language, locale, etc. For example a format of'YYYY-MM-DD HH24:MI:SS'
can be used for anIMMUTABLE
form of the function since, regardless of locale settings, the result of the function is the date and time expressed solely in numeric form. However, a format of'DD-MON-YYYY'
cannot be used for anIMMUTABLE
form of the function because the 3-character abbreviation of the month may return different results depending upon the locale setting.
Format patterns that result in a non-immutable function include any variations of spelled out or abbreviated months (MONTH, MON)
, days (DAY, DY)
, median indicators (AM, PM)
, or era indicators (BC, AD)
.
For the following example, a table with a TIMESTAMP
column is created.
The following shows the successful creation of an index with the IMMUTABLE
form of the TO_CHAR
function.
The following results in an error because the format specified in the TO_CHAR
function prevents the use of the IMMUTABLE
form since the 3-character month abbreviation, MON
, may result in different return values based on the locale setting.
TO_NUMBER
The following table lists the template patterns available for formatting numeric values:
Pattern | Description |
---|---|
9 | Value with the specified number of digits |
0 | Value with leading zeroes |
. (period) | Decimal point |
, (comma) | Group (thousand) separator |
$ | Dollar sign |
PR | Negative value in angle brackets |
S | Sign anchored to number (uses locale). |
L | Currency symbol (uses locale) |
D | Decimal point (uses locale) |
G | Group separator (uses locale) |
MI | Minus sign specified in right-most position (if number < 0) |
RN or rn | Roman numeral (input between 1 and 3999) |
V | Shift specified number of digits (see notes) |
Note that 9 results in a value with the same number of digits as there are 9s
. If a digit is not available, it outputs a space. TH
does not convert values less than zero and does not convert fractional numbers.
V
effectively multiplies the input values by 10n, where n
is the number of digits following V
. TO_CHAR
does not support the use of V
combined with a decimal point. (E.g., 99.9V99
is not allowed.)