TO_CHAR - Amazon Redshift
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

TO_CHAR

TO_CHAR converts a timestamp or numeric expression to a character-string data format.

Syntax

TO_CHAR (timestamp_expression | numeric_expression , 'format')

Arguments

timestamp_expression

An expression that results in a TIMESTAMP or TIMESTAMPTZ type value or a value that can implicitly be coerced to a timestamp.

numeric_expression

An expression that results in a numeric data type value or a value that can implicitly be coerced to a numeric type. For more information, see Numeric types. TO_CHAR inserts a space to the left of the numeral string.

Note

TO_CHAR does not support 128-bit DECIMAL values.

format

The format for the new value. For valid formats, see Datetime format strings and Numeric format strings.

Return type

VARCHAR

Examples

The following example converts a timestamp to a value with the date and time in a format with the name of the month padded to nine characters, the name of the day of the week, and the day number of the month.

select to_char(timestamp '2009-12-31 23:15:59', 'MONTH-DY-DD-YYYY HH12:MIPM'); to_char ------------------------- DECEMBER -THU-31-2009 11:15PM

The following example converts a timestamp to a value with day number of the year.

select to_char(timestamp '2009-12-31 23:15:59', 'DDD'); to_char ------------------------- 365

The following example converts a timestamp to an ISO day number of the week.

select to_char(timestamp '2022-05-16 23:15:59', 'ID'); to_char ------------------------- 1

The following example extracts the month name from a date.

select to_char(date '2009-12-31', 'MONTH'); to_char ------------------------- DECEMBER

The following example converts each STARTTIME value in the EVENT table to a string that consists of hours, minutes, and seconds.

select to_char(starttime, 'HH12:MI:SS') from event where eventid between 1 and 5 order by eventid; to_char ---------- 02:30:00 08:00:00 02:30:00 02:30:00 07:00:00

The following example converts an entire timestamp value into a different format.

select starttime, to_char(starttime, 'MON-DD-YYYY HH12:MIPM') from event where eventid=1; starttime | to_char ---------------------+--------------------- 2008-01-25 14:30:00 | JAN-25-2008 02:30PM

The following example converts a timestamp literal to a character string.

select to_char(timestamp '2009-12-31 23:15:59','HH24:MI:SS'); to_char ---------- 23:15:59

The following example converts a decimal number to a character string.

select to_char(125.8, '999.99'); to_char --------- 125.80

The following example converts a decimal number to a character string.

select to_char(125.8, '999D99'); to_char --------- 125.80

The following example converts a number to a character string with a leading zero.

select to_char(125.8, '0999D99'); to_char --------- 0125.80

The following example converts a number to a character string with the negative sign at the end.

select to_char(-125.8, '999D99S'); to_char --------- 125.80-

The following example converts a number to a character string with the positive or negative sign at the specified position.

select to_char(125.8, '999D99SG'); to_char --------- 125.80+

The following example converts a number to a character string with the positive sign at the specified position.

select to_char(125.8, 'PL999D99'); to_char --------- + 125.80

The following example converts a number to a character string with the currency symbol.

select to_char(-125.88, '$S999D99'); to_char --------- $-125.88

The following example converts a number to a character string with the currency symbol in the specified position.

select to_char(-125.88, 'S999D99L'); to_char --------- -125.88$

The following example converts a number to a character string using a thousands (comma) separator.

select to_char(1125.8, '9,999.99'); to_char --------- 1,125.80

The following example converts a number to a character string using angle brackets for negative numbers.

select to_char(-125.88, '$999D99PR'); to_char --------- $<125.88>

The following example converts a number to a Roman numeral string.

select to_char(125, 'RN'); to_char --------- CXXV

The following example converts a date to a century code.

select to_char(date '2020-12-31', 'CC'); to_char --------- 21

The following example displays the day of the week.

SELECT to_char(current_timestamp, 'FMDay, FMDD HH12:MI:SS'); to_char ----------------------- Wednesday, 31 09:34:26

The following example displays the ordinal number suffix for a number.

SELECT to_char(482, '999th'); to_char ----------------------- 482nd

The following example subtracts the commission from the price paid in the sales table. The difference is then rounded up and converted to a roman numeral, shown in the to_char column:

select salesid, pricepaid, commission, (pricepaid - commission) as difference, to_char(pricepaid - commission, 'rn') from sales group by sales.pricepaid, sales.commission, salesid order by salesid limit 10; salesid | pricepaid | commission | difference | to_char ---------+-----------+------------+------------+----------------- 1 | 728.00 | 109.20 | 618.80 | dcxix 2 | 76.00 | 11.40 | 64.60 | lxv 3 | 350.00 | 52.50 | 297.50 | ccxcviii 4 | 175.00 | 26.25 | 148.75 | cxlix 5 | 154.00 | 23.10 | 130.90 | cxxxi 6 | 394.00 | 59.10 | 334.90 | cccxxxv 7 | 788.00 | 118.20 | 669.80 | dclxx 8 | 197.00 | 29.55 | 167.45 | clxvii 9 | 591.00 | 88.65 | 502.35 | dii 10 | 65.00 | 9.75 | 55.25 | lv

The following example adds the currency symbol to the difference values shown in the to_char column:

select salesid, pricepaid, commission, (pricepaid - commission) as difference, to_char(pricepaid - commission, 'l99999D99') from sales group by sales.pricepaid, sales.commission, salesid order by salesid limit 10; salesid | pricepaid | commission | difference | to_char --------+-----------+------------+------------+------------ 1 | 728.00 | 109.20 | 618.80 | $ 618.80 2 | 76.00 | 11.40 | 64.60 | $ 64.60 3 | 350.00 | 52.50 | 297.50 | $ 297.50 4 | 175.00 | 26.25 | 148.75 | $ 148.75 5 | 154.00 | 23.10 | 130.90 | $ 130.90 6 | 394.00 | 59.10 | 334.90 | $ 334.90 7 | 788.00 | 118.20 | 669.80 | $ 669.80 8 | 197.00 | 29.55 | 167.45 | $ 167.45 9 | 591.00 | 88.65 | 502.35 | $ 502.35 10 | 65.00 | 9.75 | 55.25 | $ 55.25

The following example lists the century in which each sale was made.

select salesid, saletime, to_char(saletime, 'cc') from sales order by salesid limit 10; salesid | saletime | to_char ---------+---------------------+--------- 1 | 2008-02-18 02:36:48 | 21 2 | 2008-06-06 05:00:16 | 21 3 | 2008-06-06 08:26:17 | 21 4 | 2008-06-09 08:38:52 | 21 5 | 2008-08-31 09:17:02 | 21 6 | 2008-07-16 11:59:24 | 21 7 | 2008-06-26 12:56:06 | 21 8 | 2008-07-10 02:12:36 | 21 9 | 2008-07-22 02:23:17 | 21 10 | 2008-08-06 02:51:55 | 21

The following example converts each STARTTIME value in the EVENT table to a string that consists of hours, minutes, seconds, and time zone.

select to_char(starttime, 'HH12:MI:SS TZ') from event where eventid between 1 and 5 order by eventid; to_char ---------- 02:30:00 UTC 08:00:00 UTC 02:30:00 UTC 02:30:00 UTC 07:00:00 UTC

The following example shows formatting for seconds, milliseconds, and microseconds.

select sysdate, to_char(sysdate, 'HH24:MI:SS') as seconds, to_char(sysdate, 'HH24:MI:SS.MS') as milliseconds, to_char(sysdate, 'HH24:MI:SS:US') as microseconds; timestamp | seconds | milliseconds | microseconds --------------------+----------+--------------+---------------- 2015-04-10 18:45:09 | 18:45:09 | 18:45:09.325 | 18:45:09:325143