TO_CHAR

Converts date/time and numeric values into text strings.

Converts date/time and numeric values into text strings.

Behavior type

Stable

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 Template patterns for date/time formatting.

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

See also

DATE_PART