Cloud

to_char

The to_char function formats various data types, including date/time, integer, float point and numeric into a formatted string.

Syntax

The syntax for this function is:

Timestamp
TO_CHAR(timestamp, format_string)
Interval
TO_CHAR(interval, format_string)

Arguments

  • timestamp: TIMESTAMP or TIMESTAMP WITH TIMEZONE value to format.

  • format: Format of the output string.

If the format string is NULL, to_char returns an empty string ('') instead of NULL. This behavior is compatible with PostgreSQL.

Supported formats

The string format supports these template patterns (case-insensitive):

Pattern Description

YYYY

Year (1-9999)

MM

Month number (01–12)

DD

Day of month (01–31)

HH

Hour of day (1–12)

HH12

Hour of day (1–12)

HH24

Hour of day (0–23)

MI

Minute (0–59)

SS

Second (0–59)

MS

Millisecond (0–999)

US

Microsecond (0–999999)

AM, am, PM or pm

Meridiem indicator without periods

A.M., a.m., P.M. or p.m.

Meridiem indicator with periods

General restrictions

  • All text inside double quote "{text}" will not be considered a pattern

  • The quote character (") will not appear in the result string

  • Any text that is not a template pattern is copied verbatim (for example, preserved in the result string)

Interval overflow restrictions

Interval overflow occurs when an operation involving interval values exceeds the maximum limits of the interval data type, resulting in an error or unexpected behavior. This can happen when adding, subtracting or multiplying interval values that lead to a representation that goes beyond the allowable range for any of its components (for example, years, months, days, hours, minutes and seconds). When executing the to_char function for intervals, it is important to be aware of these overflow restrictions:

Conversion Source Component Target Component

Days to Months

Days

Months

Hours to Days

Hours

Days

Seconds to Days

Seconds

Days

All in all, for intervals the date overflow doesn’t apply (units smaller than an hour can only overflow into hours, but not into days and so on), any excess units will not carry over to the next larger unit.

Examples

Intervals

This query converts an interval and displays it in a specified string format:

Month_to_Year
SELECT TO_CHAR('25 months'::INTERVAL,'"YEAR:" YYYY "MONTH:" MM') AS FORMATTED_INTERVAL;
Hour_to_Day
SELECT TO_CHAR('13 days' + '49 hours'::INTERVAL, '"Day:" DD "Hour:" HH') AS FORMATTED_INTERVAL;
Second_to_Minute
SELECT TO_CHAR('65 seconds'::INTERVAL, '"MINUTE": MI "SECOND": SS') AS FORMATTED_INTERVAL;

Outputs:

Month_to_Year
          FORMATTED_INTERVAL
---------------------------------------
YEAR: 0002 MONTH: 01
Hour_to_Day
          FORMATTED_INTERVAL
---------------------------------------
Day: 13 Hour: 01
Second_to_Minute
          FORMATTED_INTERVAL
---------------------------------------
MINUTE: 01 SECOND: 05

Timestamps

This query retrieves the current timestamp and displays it in a specified string format:

Timestamp
SELECT TO_CHAR(CURRENT_TIMESTAMP(), '"YEAR:" YYYY "MONTH:" MM "DAY:" DD') AS FORMATTED_TIMESTAMP;
Timestamp_with_Microseconds
SELECT TO_CHAR(CURRENT_TIMESTAMP(), 'YYYY-MM-DD HH24:MI:SS.US') AS FORMATTED_TIMESTAMP;
Timestamp_with_Meridiem
SELECT TO_CHAR(CURRENT_TIMESTAMP(), 'YYYY-MM-DD HH12:MI:SS a.m.') AS FORMATTED_TIMESTAMP;

Outputs:

Timestamp
          FORMATTED_TIMESTAMP
---------------------------------------
YEAR:2025 MONTH:01 DAY:01
Timestamp_with_Microseconds
          FORMATTED_TIMESTAMP
---------------------------------------
2025-01-01 08:08:03.001200
Timestamp_with_Meridiem
          FORMATTED_TIMESTAMP
---------------------------------------
2025-01-01 08:08:03 p.m.