TO_CHAR

Converts a DATE, DATETIME, DATETIME(6), TIME, TIMESTAMP or TIMESTAMP(6) value to a string.

Syntax

TO_CHAR ( convert_from_value, result_format )

Arguments

convert_from_value

A value of one of the following data types: DATE, DATETIME, DATETIME(6), TIME, TIMESTAMP or TIMESTAMP(6) .

result_format

A format string, comprised of one or more of the format specifiers listed in the table below.

Format Specifiers

Meaning Format Specifier
Four digit year YYYY
Two digit year (20th century for 00-49) RR
Three letter month (Jan - Dec) MON
Month (January - December) MONTH
Month as a digit (1 - 12) MM
Three letter day (Sun - Sat) DY
Day (1 - 31) DD
Hour (0 - 23) HH24
Hour (1 - 12) HH or HH12
Minute (0 - 59) MI
Second (0 - 59) SS
Precision for seconds FFn, where 1 <= n <= 9 and n is the number of decimals of precision.
If n is excluded, indicates two decimals of precision.
AM or PM AM, PM, A.M., P.M.
Info

TO_CHAR’s format string may contain characters that are not format specifiers. These characters appear as literals in the resulting string.

Examples

The following examples use the :> operator to cast a string to a DATE, TIMESTAMP, or similiar type. TO_CHAR then converts the casted type to a string.

Call to TO_CHAR Result
SELECT TO_CHAR('2018-03-01' :> DATE, 'MM/DD'); 03/01
SELECT TO_CHAR('2018-03-01' :> DATE, 'The year is YYYY'); The year is 2018
SELECT TO_CHAR('2018-03-01 05:10:38' :> DATE, 'MM/DD/YYYY'); 03/01/2018
SELECT TO_CHAR('2018-03-01 05:10:38' :> DATE, 'DY MONTH DD, YYYY'); Thu Mar 01, 2018
SELECT TO_CHAR('2018-03-01 05:10:38' :> DATETIME, 'MM/DD/YYYY'); 03/01/2018
SELECT TO_CHAR('2018-03-01 05:10:38.123456' :> DATETIME(6), 'MM/DD/YYYY HH:MI:SS:FF4'); 03/01/2018 05:10:38:1234
SELECT TO_CHAR('05:10:38' :> TIME, 'The time is HH:MM:SS'); The time is 05:10:38
SELECT TO_CHAR('18-03-01' :> TIMESTAMP, 'MM/DD/YYYY'); 03/01/2018
SELECT TO_CHAR('18-03-01 05:10:38.123456' :> TIMESTAMP(6), 'MM/DD/YYYY HH:MI:SS:FF4'); 03/01/2018 05:10:38.1234
Info

When you call TO_CHAR and don’t explicitly type-cast the first argument, TO_CHAR will implicitly type-cast the first argument to TIMESTAMP(6).

When you call TO_CHAR and specify a temporal-type column in the first argument, no typecast is needed.

Was this article useful?