TO_CHAR

Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

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.`

Important

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 similar 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`

Important

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.

Last modified: April 24, 2021