Sign inTry Free

TO_CHAR

On this page

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.

Last modified: April 24, 2021

Was this article helpful?

On this page

Was this article helpful?

Verification instructions

Note: You must install cosign to verify the authenticity of the SingleStore file.

Use the following steps to verify the authenticity of singlestoredb-server, singlestoredb-toolbox, singlestoredb-studio, and singlestore-client SingleStore files that have been downloaded.

You may perform the following steps on any computer that can run cosign, such as the main deployment host of the cluster.

  1. (Optional) Run the following command to view the associated signature files.

    curl undefined

  2. Download the signature file from the SingleStore release server.

    • Option 1: Click the Download Signature button next to the SingleStore file.

    • Option 2: Copy and paste the following URL into the address bar of your browser and save the signature file.

    • Option 3: Run the following command to download the signature file.

      curl -O undefined

  3. After the signature file has been downloaded, run the following command to verify the authenticity of the SingleStore file.

    echo -n undefined |
        cosign verify-blob --certificate-oidc-issuer https://oidc.eks.us-east-1.amazonaws.com/id/CCDCDBA1379A5596AB5B2E46DCA385BC \
          --certificate-identity https://kubernetes.io/namespaces/freya-production/serviceaccounts/job-worker \
          --bundle undefined \
          --new-bundle-format -
    Verified OK

Try Out This Notebook to See What’s Possible in SingleStore

Get access to other groundbreaking datasets and engage with our community for expert advice.

Sign UpLog In