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


TO_CHAR ( convert_from_value, result_format )


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

  • result_format: 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 YY
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
Day of the week as a digit (1 - 7) D
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.

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


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
SELECT TO_CHAR('2018-04-01 08:00:00.123456' :> DATETIME(6), 'PM'); AM

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?