TO_CHAR

Converts 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:

The following restrictions apply:

TO_CHAR does not support binary data types BINARY and VARBINARY
TO_CHAR does not support the use of V combined with a decimal point—for example, 99.9V99

pattern

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

Notes

Vertica pads TO_CHAR output with a leading space, so positive and negative values have the same length. To suppress padding, use the FM prefix.

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'), TO_CHAR(TIMETZ '14:34:06.4+6','HH12:MI am');
 TO_CHAR  | TO_CHAR
----------+----------
 02:34 pm | 04:34 am
(1 row)

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. In the following example, YYYY is replaced by the year data, but the Y in Year is not:
```
=> SELECT to_char(CURRENT_TIMESTAMP, '"Hello Year " YYYY');
     to_char
------------------
 Hello Year  2021
(1 row)
```
TO_CHAR uses different day-of-the-week numbering (see the D template pattern) than EXTRACT.
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 include a double quote (") character in 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\\"'
```
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

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