TO_CHAR

TO_CHAR converts various date/time and numeric values into text strings.

Syntax

TO_CHAR ( expression, pattern )

Semantics

expression	(TIMESTAMP, INTERVAL, INTEGER, DOUBLE PRECISION) specifies the value to convert
pattern	(CHAR or VARCHAR) specifies an output pattern string using the Template Patterns for Date/Time Formatting and/or Template Patterns for Numeric Formatting.

Notes

Ordinary text is allowed in TO_CHAR templates and is output literally. You can put a substring in double quotes to force it to be interpreted as literal text even if it contains pattern key words. For example, in '"Hello Year "YYYY', the YYYY is replaced by the year data, but the single Y in Year is not.
TO_CHAR's day of the week numbering (see the 'D' template pattern) is different from that of the EXTRACT function
Given an INTERVAL type, TO_CHAR formats HH and HH12 as hours in a single day, while HH24 can output hours exceeding a single day, for example, >24
To use a double quote character in the output, precede it with a double backslash. This is necessary because the backslash already has a special meaning in a string constant. For example: '\\"YYYY Month\\"'
TO_CHAR does not support the use of V combined with a decimal point. For example: 99.9V99 is not allowed.

Examples

Expression	Result
`TO_CHAR(CURRENT_TIMESTAMP, 'Day, DD HH12:MI:SS')`	`'Tuesday , 06 05:39:18'`
`TO_CHAR(CURRENT_TIMESTAMP, 'FMDay, 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(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, '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, '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:"999" Post:" .999')`	`'Pre: 485 Post: .800'`
`TO_CHAR(12, '99V999')`	`' 12000'`
`TO_CHAR(12.4, '99V999')`	`' 12400'`
`TO_CHAR(12.45, '99V9')`	`' 125'`