TO_CHAR

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Converts a timestamp or interval value to a formatted text string using a template pattern.

The first argument depends on the overload used:

• <timestamp>: A value of type timestamp or timestamp with time zone.
• <interval>: A value of type interval.

The second argument applies to both overloads:

• <format>: A text template string containing one or more template patterns that define the output format.

Returns text. Returns NULL if either argument is NULL.

In a to_char output template string, recognized patterns are replaced with appropriately formatted data based on the given value. Any text that is not a template pattern is copied verbatim.

Pattern	Description
HH	Hour of day on a 12-hour clock (01–12)
HH12	Hour of day on a 12-hour clock (01–12)
HH24	Hour of day on a 24-hour clock (00–23)
MI	Minutes (00–59)
SS	Seconds (00–59)
MS	Milliseconds (000–999)
US	Microseconds (000000–999999)
SSSS	Seconds elapsed since midnight (0–86399)
AM, am, PM or pm	Meridiem indicator without periods (for example, AM or PM)
A.M., a.m., P.M. or p.m.	Meridiem indicator with periods (for example, A.M. or P.M.)
Y,YYY	Full year (4 or more digits) with a comma separator (for example, 2,024)
YYYY	Full year (4 or more digits, for example, 2024)
YYY	Last 3 digits of the year
YY	Last 2 digits of the year
Y	Last digit of the year
IYYY	ISO 8601 week-numbering year, 4 or more digits
IYY	Last 3 digits of the ISO 8601 week-numbering year
IY	Last 2 digits of the ISO 8601 week-numbering year
I	Last digit of the ISO 8601 week-numbering year
BC, bc, AD or ad	Era indicator without periods (for example, BC or AD)
B.C., b.c., A.D. or a.d.	Era indicator with periods (for example, B.C. or A.D.)
MONTH	Full month name in upper case, blank-padded to 9 characters (for example, JANUARY)
Month	Full month name with initial capital, blank-padded to 9 characters (for example, January)
month	Full month name in lower case, blank-padded to 9 characters (for example, january)
MON	Abbreviated month name in upper case, 3 characters (for example, JAN)
Mon	Abbreviated month name with initial capital, 3 characters (for example, Jan)
mon	Abbreviated month name in lower case, 3 characters (for example, jan)
MM	Month number (01–12)
DAY	Full day name in upper case, blank-padded to 9 characters (for example, MONDAY)
Day	Full day name with initial capital, blank-padded to 9 characters (for example, Monday)
day	Full day name in lower case, blank-padded to 9 characters (for example, monday)
DY	Abbreviated day name in upper case, 3 characters (for example, MON)
Dy	Abbreviated day name with initial capital, 3 characters (for example, Mon)
dy	Abbreviated day name in lower case, 3 characters (for example, mon)
DDD	Day of the year (001–366)
IDDD	Day of the ISO 8601 week-numbering year (001–371; day 1 is Monday of the first ISO week)
DD	Day of the month (01–31)
D	Day of the week, where Sunday is 1 and Saturday is 7
ID	ISO 8601 day of the week, where Monday is 1 and Sunday is 7
W	Week of the month (1–5); the first week starts on the first day of the month
WW	Week number of the year (1–53); the first week starts on the first day of the year
IW	ISO 8601 week number (01–53); week 1 contains the first Thursday of the year
CC	Century as 2 digits (for example, 21 for the years 2001–2100)
J	Julian Day — integer count of days since November 24, 4714 BC at midnight UTC
Q	Quarter of the year (1–4)
RM	Month in upper case Roman numerals (I–XII, where I = January)
rm	Month in lower case Roman numerals (i–xii, where i = January)
TZ	Time zone abbreviation in upper case (for example, PST); output only
tz	Time zone abbreviation in lower case (for example, pst); output only
TZH	Time zone offset hours from UTC (for example, -07)
TZM	Time zone offset minutes from UTC (for example, 30)
OF	Time zone offset from UTC in hours and minutes (for example, -07:00); output only

Modifiers can be applied to any template pattern to alter its behavior. For example, FMMonth is the Month pattern with the FM modifier.

Modifier	Description	Example
FM prefix	fill mode (suppress leading zeroes and padding blanks)	FMMonth
TH suffix	upper case ordinal number suffix	DDTH, for example, 12TH
th suffix	lower case ordinal number suffix	DDth, for example, 12th

• FM suppresses leading zeroes and trailing blanks that are added to make the output of a pattern fixed-width. FM modifies only the next specification, and repeated FM modifiers toggle fill mode on and off.

• Text in to_char templates is output literally. Put a substring in double quotes to force it to be interpreted as literal text even if it contains template patterns. For example, in '"Hello Year "YYYY', the YYYY is replaced by the year data, but the single Y in Year is not replaced.

• To include a double quote in the output, precede it with a backslash. For example, '\"YYYY Month\"'. Within a double-quoted string, a backslash causes the next character to be taken literally.

• to_char can use a mixture of Gregorian and ISO week-numbering date fields, but values such as IYYY-MM-DD yield mixed results near the start of the year.

Mixing ISO 8601 week-numbering fields (IYYY, IW, ID, IDDD) with Gregorian date fields in the same template can produce unexpected results.

• to_char(..., 'ID') day-of-week numbering matches extract(isodow from ...), but to_char(..., 'D') does not match extract(dow from ...) numbering.

• to_char(interval) formats HH and HH12 as shown on a 12-hour clock. Zero hours and 36 hours both output as 12. HH24 outputs the full hour value, which can exceed 23 in an interval value.

Format the current timestamp as a day-of-week and time string.

Without fill mode:

Returns a string such as 'Tuesday , 06 05:39:18'.

With fill mode (FM) to suppress padding:

Returns a string such as 'Tuesday, 6 05:39:18'.

Format a 15-hour 2-minute 12-second interval as a 24-hour time string.

Returns '15:02:12'.

Display the day of the month with an ordinal suffix and the full month name.

Returns '5th March 2024'.

• TO_DATE
• TO_TIMESTAMP
• Formatting Functions