Data Type Formatting Functions v11

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.

FunctionReturn TypeDescriptionExampleResult
TO_CHAR(DATE [, format ])VARCHAR2Convert 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 ])VARCHAR2Convert 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 ])VARCHAR2Convert an integer to a string with output, formatTO_CHAR(2412, '999,999S')2,412+
TO_CHAR(NUMBER [, format ])VARCHAR2Convert a decimal number to a string with output, formatTO_CHAR(10125.35, '999,999.99')10,125.35
TO_CHAR(DOUBLE PRECISION, format)VARCHAR2Convert a floating-point number to a string with output, formatTO_CHAR (CAST(123.5282 AS REAL), '999.99')123.53
TO_DATE(string [, format ])TIMESTAMPConvert a date or timestamp formatted string to a TIMESTAMP data typeTO_DATE('2007-07-04 13:39:10', 'YYYY-MM-DD HH24:MI:SS')

04-JUL-07 13:39:10

04-JUL-07 00:00:00
TO_NUMBER(string [, format ])NUMBERConvert a number formatted string to a NUMBER data typeTO_NUMBER('2,412-', '999,999S')-2412
TO_TIMESTAMP(string, format)TIMESTAMPTZConvert a timestamp formatted string to a TIMESTAMPTZ data typeTO_TIMESTAMP('05 Dec 2000 08:30:25 pm', 'DD Mon YYYY hh12:mi:ss pm')05-DEC-00 20:30:25 +05:30

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.

The following table shows the template patterns available for formatting date values using the TO_CHAR, TO_DATE, and TO_TIMESTAMP functions.

HHHour of day (01-12)
HH12Hour of day (01-12)
HH24Hour of day (00-23)
MIMinute (00-59)
SSSecond (00-59)
SSSSSSeconds past midnight (0-86399)
FFnFractional 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,YYYYear (4 and more digits) with comma
YEARYear (spelled out)
SYEARYear (spelled out) (BC dates prefixed by a minus sign)
YYYYYear (4 and more digits)
SYYYYYear (4 and more digits) (BC dates prefixed by a minus sign)
YYYLast 3 digits of year
YYLast 2 digits of year
YLast digit of year
IYYYISO year (4 and more digits)
IYYLast 3 digits of ISO year
IYLast 2 digits of ISO year
ILast 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)
MONTHFull uppercase month name
MonthFull mixed-case month name
monthFull lowercase month name
MONAbbreviated uppercase month name (3 chars in English, localized lengths vary)
MonAbbreviated mixed-case month name (3 chars in English, localized lengths vary)
monAbbreviated lowercase month name (3 chars in English, localized lengths vary)
MMMonth number (01-12)
DAYFull uppercase day name
DayFull mixed-case day name
dayFull lowercase day name
DYAbbreviated uppercase day name (3 chars in English, localized lengths vary)
DyAbbreviated mixed-case day name (3 chars in English, localized lengths vary)
dyAbbreviated lowercase day name (3 chars in English, localized lengths vary)
DDDDay of year (001-366)
DDDay of month (01-31)
DDay of week (1-7; Sunday is 1)
WWeek of month (1-5) (The first week starts on the first day of the month)
WWWeek number of year (1-53) (The first week starts on the first day of the year)
IWISO week number of year; the first Thursday of the new year is in week 1
CCCentury (2 digits); the 21st century starts on 2001-01-01
SCCSame as CC except BC dates are prefixed by a minus sign
JJulian Day (days since January 1, 4712 BC)
RMMonth in Roman numerals (I-XII; I=January) (uppercase)
rmMonth in Roman numerals (i-xii; i=January) (lowercase)
RRFirst 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.
RRRROnly 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.

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.

FM prefixFill mode (suppress padding blanks and zeros)FMMonth
TH suffixUppercase ordinal number suffixDDTH
th suffixLowercase ordinal number suffixDDth
FX prefixFixed format global option (see usage notes)FX Month DD Day
SP suffixSpell modeDDSP

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 and TO_DATE skip multiple blank spaces in the input string if the FX option is not used. FX must be specified as the first item in the template. For example:

    TO_TIMESTAMP('2000 - JUN', 'YYYY-MON') is correct, but
    TO_TIMESTAMP('2000    JUN', 'FXYYYY MON') returns an error
    because TO_TIMESTAMP expects one space only.
  • Ordinary text is allowed in TO_CHAR templates and will be output literally.

  • In conversions from string to timestamp or date, the CC field is ignored if there is a YYY, YYYY or Y,YYY field. If CC is used with YY or Y then the year is computed as (CC-1)*100+YY.

The following table shows the template patterns available for formatting numeric values.

9Value with the specified number of digits
0Value with leading zeroes
. (period)Decimal point
, (comma)Group (thousand) separator
$Dollar sign
PRNegative value in angle brackets
SSign anchored to number (uses locale)
LCurrency symbol (uses locale)
DDecimal point (uses locale)
GGroup separator (uses locale)
MIMinus sign specified in righ-most position (if number < 0)
RN or rnRoman numeral (input between 1 and 3999)
VShift specified number of digits (see notes)

Usage notes for numeric formatting:

  • 9 results in a value with the same number of digits as there are 9s. If a digit is not available it ouputs 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.)

The following table shows some examples of the use of the TO_CHAR and TO_DATE functions:

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(5.2, 'FMRN')'V'
TO_CHAR(12, '99V999')' 12000'
TO_CHAR(12.4, '99V999')' 12400'
TO_CHAR(12.45, '99V9')' 125'


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:

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 type TIMESTAMP.
  • 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 an IMMUTABLE 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 an IMMUTABLE 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.

edb=# CREATE INDEX ts_idx ON ts_tbl (TO_CHAR(ts_col,'YYYY-MM-DD HH24:MI:SS'));
edb=# \dS ts_idx
                                   Index "public.ts_idx"
 Column  |       Type        |                         Definition
 to_char | character varying | to_char(ts_col, 'YYYY-MM-DD HH24:MI:SS'::character varying)
btree, for table "public.ts_tbl"

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.

edb=# CREATE INDEX ts_idx_2 ON ts_tbl (TO_CHAR(ts_col, 'DD-MON-YYYY'));
ERROR:  functions in index expression must be marked IMMUTABLE