TO_CHAR

Overview

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

Syntax

The syntax for using the TO_CHAR function is as follows:

Timestamp

TO_CHAR(timestamp, format_string)

Arguments

• timestamp: TIMESTAMP or TIMESTAMP WITH TIMEZONE value to be formatted
• format: format of the output string

Supported Formats

The string format supports the following 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 simply copied verbatim i.e. 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 i.e. years, months, days, hours, minutes and seconds. When executing the TO_CHAR function for intervals, it is important to be aware of the following 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;

Here are the outputs for the queries presented above:

Month_to_Year

          FORMATTED_INTERVAL                
---------------------------------------
YEAR: 0002 MONTH: 01

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;

Here are the outputs for the queries presented above:

Timestamp

          FORMATTED_TIMESTAMP                
---------------------------------------
YEAR:2025 MONTH:01 DAY:01