TO_CHAR

Converts various date/time and numeric values into text strings.

Behavior Type

Syntax

TO_CHAR ( expression [, pattern ] )

Parameters

expression

Specifies the value to convert, one of the following data types:

pattern

A CHAR or VARCHAR that specifies an output pattern string. See:

Notes

TO_CHAR(any) casts any type, except BINARY/VARBINARY, to VARCHAR.

The following example returns an error if you try to cast TO_CHAR to a binary data type:
```
=> SELECT TO_CHAR('abc'::VARBINARY);
   ERROR:  cannot cast type varbinary to varchar
```

TO_CHAR accepts TIME and TIMETZ data types as inputs if you explicitly cast TIME to TIMESTAMP and TIMETZ to TIMESTAMPTZ.

=> SELECT TO_CHAR(TIME '14:34:06.4','HH12:MI am');
=> SELECT TO_CHAR(TIMETZ '14:34:06.4+6','HH12:MI am');

You can extract the timezone hour from TIMETZ:

=> SELECT EXTRACT(timezone_hour FROM TIMETZ '10:30+13:30');
 date_part
-----------
        13
(1 row)

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.
When rounding, the last digit of the rounded representation is selected to be even if the number is exactly half way between the two.

Examples

Expression	Result
SELECT TO_CHAR(CURRENT_TIMESTAMP, 'Day, DD HH12:MI:SS');	'Tuesday , 06 05:39:18'
SELECT TO_CHAR(CURRENT_TIMESTAMP, 'FMDay, FMDD HH12:MI:SS');	'Tuesday, 6 05:39:18'
SELECT TO_CHAR(TIMEtz '14:34:06.4+6','HH12:MI am'); TO_CHAR	04:34 am
SELECT TO_CHAR(-0.1, '99.99');	' -.10'
SELECT TO_CHAR(-0.1, 'FM9.99');	'-.1'
SELECT TO_CHAR(0.1, '0.9');	' 0.1'
SELECT TO_CHAR(12, '9990999.9');	' 0012.0'
SELECT TO_CHAR(12, 'FM9990999.9');	'0012.'
SELECT TO_CHAR(485, '999');	' 485'
SELECT TO_CHAR(-485, '999');	'-485'
SELECT TO_CHAR(485, '9 9 9');	' 4 8 5'
SELECT TO_CHAR(1485, '9,999');	' 1,485'
SELECT TO_CHAR(1485, '9G999');	' 1 485'
SELECT TO_CHAR(148.5, '999.999');	' 148.500'
SELECT TO_CHAR(148.5, 'FM999.999');	'148.5'
SELECT TO_CHAR(148.5, 'FM999.990');	'148.500'
SELECT TO_CHAR(148.5, '999D999');	' 148,500'
SELECT TO_CHAR(3148.5, '9G999D999');	' 3 148,500'
SELECT TO_CHAR(-485, '999S');	'485-'
SELECT TO_CHAR(-485, '999MI');	'485-'
SELECT TO_CHAR(485, '999MI');	'485 '
SELECT TO_CHAR(485, 'FM999MI');	'485'
SELECT TO_CHAR(485, 'PL999');	'+485'
SELECT TO_CHAR(485, 'SG999');	'+485'
SELECT TO_CHAR(-485, 'SG999');	'-485'
SELECT TO_CHAR(-485, '9SG99');	'4-85'
SELECT TO_CHAR(-485, '999PR');	'<485>'
SELECT TO_CHAR(485, 'L999');	'DM 485
SELECT TO_CHAR(485, 'RN');	' CDLXXXV'
SELECT TO_CHAR(485, 'FMRN');	'CDLXXXV'
SELECT TO_CHAR(5.2, 'FMRN');	'V'
SELECT TO_CHAR(482, '999th');	' 482nd'
SELECT TO_CHAR(485, '"Good number:"999');	'Good number: 485'
SELECT TO_CHAR(485.8, '"Pre:"999" Post:" .999');	'Pre: 485 Post: .800'
SELECT TO_CHAR(12, '99V999');	' 12000'
SELECT TO_CHAR(12.4, '99V999');	' 12400'
SELECT TO_CHAR(12.45, '99V9');	' 125'
SELECT TO_CHAR(-1234.567);	-1234.567
SELECT TO_CHAR('1999-12-25'::DATE);	1999-12-25
SELECT TO_CHAR('1999-12-25 11:31'::TIMESTAMP);	1999-12-25 11:31:00
SELECT TO_CHAR('1999-12-25 11:31 EST'::TIMESTAMPTZ);	1999-12-25 11:31:00-05
SELECT TO_CHAR('3 days 1000.333 secs'::INTERVAL);	3 days 00:16:40.333