`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.

<fiscal_option>: One or more fiscal calendar options that control how the fiscal template patterns (FiscalYear, FiscalQuarter, FiscalMonth, FiscalWeek) resolve to labels. If you omit these options, to_char uses the fiscal calendar configured in your org. See Fiscal Calendar Options. Fiscal options apply to the timestamp overload only.

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 isn’t 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

Fiscal template patterns resolve to fiscal calendar labels based on the fiscal calendar in effect for the query. Use the fiscal calendar options to control which calendar is applied. See Fiscal Calendar Options.

Pattern	Description
`FiscalYear`	Fiscal year label (for example, `FY 2024`)
`FiscalQuarter`	Fiscal quarter label (for example, `Quarter 1`)
`FiscalMonth`	Fiscal month label (for example, `January`)
`FiscalWeek`	Fiscal week label (for example, `Week 2`)

Fiscal patterns are whole-word, case-sensitive tokens. They’re substituted only when spelled exactly as shown. See Usage Notes for how misspelled and quoted fiscal tokens are handled.

Apply modifiers 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 isn’t 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.

If you don’t pass any fiscal calendar options, to_char uses the fiscal calendar configured in your org. If no calendar is configured, it uses a standard fiscal calendar with the default values.
Fiscal patterns are substituted only when spelled exactly (FiscalYear, FiscalQuarter, FiscalMonth, FiscalWeek). A misspelled fiscal token is parsed as ordinary format tokens. For example, FiscaMonth is parsed as the literal F, the ISO year digit i, the literal sca, and the padded month name Month.
To use a custom fiscal calendar, pass fiscal_period_table => TABLE(<table_expression>). The custom fiscal path supports only a constant fiscal pattern; a non-constant (dynamic) fiscal pattern combined with a custom fiscal table returns an error. If the input date is not present in the custom fiscal table, the fiscal patterns return NULL. The lookup matches on DayDate__c cast to date, so any time component of the input is truncated.
You can’t combine options for standard and custom fiscal calendars in the same call.

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'.

Format a timestamp with fiscal year, quarter, month, and week labels using the default fiscal calendar.

Returns 'FY 2024 Quarter 1 January Week 2'.

Apply standard fiscal calendar options to change the fiscal year start and week start.

Returns 'FY 2023 Quarter 4 December Week 50'.

Resolve fiscal labels from a custom fiscal period table. The input date is matched against the table’s DayDate__c column.

Returns the labels defined in my_fiscal_calendar for that date, such as 'Cust_FY 2025|Cust_FQ3|Cust_FM4|Cust_FW50'. If the date isn’t present in the table, the fiscal patterns return NULL.