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