Skip to main content

SingleStoreDB v8.1

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.

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.

In this section: