TO_CHAR

Converts a value of type DATE, TIMESTAMP, or TIMESTAMPTZ to a formatted string.

Syntax

TO_CHAR(<expression>, '<format>')

Parameters

Accepted <format> patterns include:

Additionally, modifiers can be applied to the format patterns above to alter their behavior.

Any character in the format string that is not recognized as a pattern is simply copied over without being replaced. Parts that are quoted " will be copied over independent of possibly valid patterns. Patterns are matched in lower and upper case if there is no other behavior described above.

Return Types

Formatted string based on the function’s specifications

Examples

The example below outputs the current local time in a formatted string. Note that the " around the words Date and Time are required, otherwise the characters D and I would be interpreted as valid patterns which would result in the output 6ate and T3me.

SELECT
    TO_CHAR(
        TIMESTAMPTZ '2023-03-02 06:33:26.466511',
        '"Date": FMMonth FMddth, YYYY "Time": FMHH12am (mi:ss.us) OF (TZ)'
    );

Returns: 'Date: March 2nd, 2023 Time: 6am (33:26.466511) -08:00 (PST)'

The example below outputs the current date in a formatted string with any time field set to 0 which indicates midnight. Note the quotation marks again that are required to prevent unintended replacements.

SELECT
    TO_CHAR(
        DATE '2023-03-02' ,
        '"The" fmDDDth "day in" YY "is a" fmDay "at midnight" hh24:mi:ss.us'
    );

Returns: 'The 61st day in 23 is a Thursday at midnight 00:00:00.000000'