TO_CHAR ​

This function converts date, time, timestamp, numeric, and boolean expressions to character strings. TO_CHAR is intended for cases where explicit casting is not feasible.

TO_CHAR( { date | time | timestamp | timestamptz }, datetime_format)
TO_CHAR( { smallint | integer | bigint | real | float | decimal }, numeric_format [, decsep, groupsep ] )
TO_CHAR(boolean, boolean_format)

You can use column references or literal values for the first argument to the function. The second argument must be a format string that specifies how to interpret each input value. For example, valid date formats for 2017-12-19 include YYYY-MM-DD and DD-MM-YYYY. The INTERVAL data type is not supported.

datetime_format

See Formats for Datetime Values.

Note: TO_CHAR(time) only supports a subset of the datetime_format patterns, specifically:

Pattern	Description
HH	Hour of day (01-12)
HH12	Hour of day (01-12)
HH24	Hour of day (00-23)
MI	Minute (00-59)
SS	Second (00-59)
MS	Millisecond (000-999)
US	Microsecond (000000-999999)
AM, am, PM, pm	Meridiem indicator (without periods)
A.M., a.m., P.M., p.m.	Meridiem indicator (with periods)

numeric_format

See Numeric Formatting.

decsep, groupsep

TO_CHAR with numeric data types supports optional characters for the decimal separator and group separator (D and G in the numeric format string). For example, you can use a comma for the decimal point and commas to separate groups. The default decimal separator is a period, and the default group separator is a comma. You must specify both or none of these separators. These arguments only apply if the appropriate characters ('G','D', ',', '.') appear in the format string.

boolean_format

See Boolean Formatting

In a format string, patterns are recognized and replaced with appropriately formatted data; any literal text in the string that is not recognized as a template pattern is copied into the result as is.

Examples with Dates and Timestamps ​

premdb=# select to_char(timestamp '1992-08-01 00:00:00', 'YYYY-MM-DD HH12:MI:SS') 
from sys.const;
      to_char       
---------------------
 1992-08-01 12:00:00
(1 row)

premdb=# select to_char(matchday, 'YYYY-MM-DD') from match 
where seasonid=14 and htid=32 order by 1;
  to_char   
------------
 2005-08-13
 2005-08-23
 2005-09-17
 2005-10-01
 2005-10-22
...

premdb1=# select matchday, to_char(matchday, 'FMDay, Month DD, YYYY') from match where seasonid=21 and htid=2 order by 1;
     matchday       |           to_char            
---------------------+------------------------------
 2012-08-18 00:00:00 | Saturday, August 18, 2012
 2012-09-15 00:00:00 | Saturday, September 15, 2012
 2012-09-29 00:00:00 | Saturday, September 29, 2012
 2012-10-27 00:00:00 | Saturday, October 27, 2012
 2012-11-10 00:00:00 | Saturday, November 10, 2012
...

Examples with Numbers ​

Return character strings for avg_att values in the team table:

premdb=# select name, to_char(avg_att, '99.99') from team where avg_att>0.0 order by 2 desc;
        name         | to_char 
----------------------+---------
 Manchester United    |  75.29
 Arsenal              |  59.94
 Manchester City      |  54.04
 Newcastle United     |  49.75
 Liverpool            |  43.91
 Sunderland           |  43.07
...

Return the top five capacity values from the team table as exponents:

premdb=# select capacity, to_char(capacity,'EEEE') from team order by 1 desc limit 5;
 capacity | to_char 
----------+---------
   75635 |  8e+04
   60260 |  6e+04
   55097 |  6e+04
   52405 |  5e+04
   49000 |  5e+04
(5 rows)

Specify non-default decimal and group separators for a numeric constant. In this example, the decimal separator is a comma and the group separator is a space.

premdb=# select to_char(1234567.77, '9G999G999D99', ',', ' ');
   to_char    
---------------
  1 234 567,77
(1 row)

Example with Boolean ​

The following example returns a 1 (true) or a 0 (false) for the values in a boolean column:

premdb=# select c1, to_char(c1, '1') from booltable;
 c1 | to_char 
----+---------
 t  | 1
 f  | 0
 t  | 1
 f  | 0
(4 rows)

Numeric Formatting ​

Note: The currency symbol (L) is not supported.

Boolean Formatting ​

Parent topic:Formatting Functions