You are viewing an older version of this section. View current production version.
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. |
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 |
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.
