To_char function Reference (RPM)

The Postgres Format function provides a set of effective tools for converting various data types (date/time, int,float,numeric) into formatted strings and, in turn, converting from formatted strings to raw data types.

Note: The second parameter of all formatting functions is the template used for the transformation.

table 5-7. Formatting functions

function	return	Description	Example
To_char (timestamp, text)	Text	Convert Timestamp to String	To_char (timestamp ' Now ', ' HH12:MI:SS ')
To_char (int, text)	Text	Convert Int4/int8 to String	To_char (125, ' 999 ')
To_char (Float, text)	Text	Convert Float4/float8 to String	To_char (125.8, ' 999d9 ')
To_char (Numeric, text)	Text	Convert numeric to String	To_char (Numeric ' -125.8 ', ' 999d99s ')
To_date (text, text)	Date	Convert String to date	To_date (' Dec ', ' DD Mon YYYY ')
To_timestamp (text, text)	Date	Convert String to Timestamp	To_timestamp (' Dec ', ' DD Mon YYYY ')
To_number (text, text)	Numeric	Convert string to Numeric	To_number (' 12,454.8-', ' 99g999d9s ')

table 5-8. Templates for Date/time conversions

Templates	Description
HH	Number of hours in the day (01-12)
HH12	Number of hours in the day (01-12)
HH24	Number of hours in the day (00-23)
MI	Minutes (00-59)
Ss	Seconds (00-59)
SSSS	Seconds after midnight (0-86399)
AM or a.m. or PM or p.m.	Noon SIGN (uppercase)
Am or a.m. or PM or p.m.	Noon Logo (lowercase)
Y,yyy	Year with commas (4 and more bits)
YYYY	Year (4 and more bits)
YYY	The latter three digits of the year
Yy	The latter two of the years
Y	The last one of the years
BC or B.C. or AD or A.D.	Year identifier (uppercase)
BC or B.C. or ad or A.D.	Year logo (lowercase)
MONTH	Full-length uppercase month name (9 characters)
Month	Full-length mixed-Case Month name (9 characters)
Month	Full-length lowercase month name (9 characters)
MON	Capital Abbreviation month name (3 characters)
Mon	Abbreviated mixed Case month name (3 characters)
Mon	lowercase abbreviation month name (3 characters)
Mm	Month (01-12)
Day	Full capitalization date name (9 characters)
Day	Full-length mixed-Case Date name (9 characters)
Day	Full-Length Lowercase date name (9 characters)
Dy	Abbreviated Capital Date name (3 characters)
Dy	Abbreviated mixed case date name (3 characters)
Dy	Abbreviated Lowercase date name (3 characters)
Ddd	The days of the Year (001-366)
Dd	One months of life (01-31)
D	Days of the Week (1-7;sun=1)
W	Number of weeks in one months
Ww	Number of weeks in a year
Cc	Century (2-bit)
J	Julian Date (dates from January 1, 4712 BC)
Q	Quarter
Rm	Roman numerals Month (I-xii;i=jan)-Uppercase
Rm	Roman numerals Month (I-xii;i=jan)-lowercase

All templates allow the use of prefix and suffix modifiers. The modifier is always allowed in the template. The prefix 'FX' is just a global modifier.

table 5-9. Suffix for date/time template to_char()

suffix	Description	Example
Fm	Fill mode prefix	Fmmonth
TH	Uppercase Order number suffix	Ddth
Th	lowercase order number suffix	Ddth
Fx	Fixed mode global options (see below)	FX Month DD Day
Sp	Spelling mode (not yet implemented)	Ddsp

Notes on usage:

• If you do not use the FX option,to_timestamp and to_date ignore whitespace. FX must be declared as the first entry in a template.
• The backslash ("\") must be used as a double backslash ("\ \"), such as ' \\HH\\MI\\SS '.
• The string between the double quotation marks (' "') is ignored and is not parsed. If you want to write double quotes to the output, you must place a double backslash (' \ \ ') in front of the double quotation mark, for example ' \ \ ' YYYY month\\'.
• To_char supports text without leading double quotation marks (' "'), but any string between double quotes is processed quickly and is also guaranteed not to be interpreted as a template keyword (for example:'" Hello Year: "YYYY ').

table 5-10. Templates for To_char(numeric)

Templates	Description
9	Value with the specified number of digits
0	The value of the leading zero
. Period	decimal Point
, (comma)	Grouping (Thousand) separators
PR	Negative value inside angle brackets
S	Negative value with minus sign (using localization)
L	Currency symbol (using localization)
D	decimal point (using localization)
G	Grouping separators (using localization)
MI	Minus sign at the indicated position (if the number < 0)
Pl	A plus sign at the indicated position (if the number > 0)
SG	Positive/negative sign at the indicated position
Rn	Roman numerals (input between 1 and 3999)
th or th	Convert to Ordinal
V	Move n bits ( decimal ) (see annotations)
Eeee	Science counts. Not supported now.

Notes on usage:

• using ' SG ', ' PL ' or ' MI ' signed words are not attached to numbers, for example, To_char ( -12, ' S9999 ') generates   ' -12 ' , and  < Strong>to_char ( -12, ' MI9999 ') generates &NBSP; '-N ' . Oracle   implementations are not allowed in   9   Front use   MI , but requires   9   in   MI   front.
• PL , SG , and   TH   is  postgres  extension.
• 9   Indicates the same number of digits as in   9   string. If no numbers are available, use a blank (space).
• TH   do not convert values less than 0, and do not convert decimal . TH   is a  postgres  extension.
• V   Conveniently multiply the input value by   10^ n , here   n   is followed   V   subsequent digits. To_char   Do not support the   V   is tied to a decimal point (for example. "99.9v99" is not allowed.)

table 5-11. To_char Example

input	Output
To_char (Now (), ' Day, HH12:MI:SS ')	' Tuesday, 05:39:18 '
To_char (Now (), ' Fmday, HH12:MI:SS ')	' Tuesday, 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 (' FM9990999.9 ')	' 0012 '
To_char (485, ' 999 ')	' 485 '
To_char (-485, ' 999 ')	'-485 '
To_char (485, ' 9 9 9 ')	' 4 8 5 '
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, ' 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, ' PL999 ')	' +485 '
To_char (485, ' SG999 ')	' +485 '
To_char ( -485, ' SG999 ')	'-485 '
To_char ( -485, ' 9sg99 ')	' 4-85 '
To_char ( -485, ' 999PR ')	' <485> '
To_char (485, ' L999 ')	' DM 485
To_char (485, ' RN ')	' CDLXXXV '
To_char (485, ' FMRN ')	' CDLXXXV '
To_char (5.2, ' FMRN ')	V
To_char (482, ' 999th ')	' 482nd '
To_char (485, ' Good number: ' 999 ')	' Good number:485 '
To_char (485.8, ' "Pre-decimal:" 999 "Post-decimal:". 999 ')	' pre-decimal:485 post-decimal:. 800 '
To_char (' 99v999 ')	' 12000 '
To_char (12.4, ' 99v999 ')	' 12400 '
To_char (12.45, ' 99v9 ')	' 125 '

To_char function Reference (RPM)