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.

Syntax

to_char(<timestamp>, <format>)
to_char(<interval>, <format>)
to_char(<timestamp>, <format> [, <fiscal_option> [, ...] ])

Arguments

Required

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.

Optional

• <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

Returns text. Returns NULL if either argument is NULL.

Template Patterns

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.

Fiscal Template Patterns

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.

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.

Modifiers

Apply modifiers to any template pattern to alter its behavior. For example, FMMonth is the Month pattern with the FM modifier.

Usage Notes

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

note: 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.

Fiscal Pattern Notes

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

Examples

Format a Timestamp

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

Without fill mode:

SELECT to_char(current_timestamp, 'Day, DD HH12:MI:SS');

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

With fill mode (FM) to suppress padding:

SELECT to_char(current_timestamp, 'FMDay, FMDD HH12:MI:SS');

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

Format an Interval

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

SELECT to_char(interval '15h 2m 12s', 'HH24:MI:SS');

Returns '15:02:12'.

Add Ordinal Suffixes to a Date

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

SELECT to_char(DATE '2024-03-05', 'FMDDth FMMonth YYYY');

Returns '5th March 2024'.

Format Fiscal Calendar Labels

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

SELECT to_char(timestamp '2024-01-09 12:00:00', 'FiscalYear FiscalQuarter FiscalMonth FiscalWeek');

Returns 'FY 2024 Quarter 1 January Week 2'.

Format Fiscal Labels with Standard Fiscal Options

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

SELECT to_char(
    timestamp '2024-01-09 12:00:00',
    'FiscalYear FiscalQuarter FiscalMonth FiscalWeek',
    fiscal_year_start_month => 2,
    first_day_of_fiscal_week => 1,
    use_start_date_as_fiscal_year_name => true
);

Returns 'FY 2023 Quarter 4 December Week 50'.

Format Fiscal Labels with a Custom Fiscal Calendar

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

SELECT to_char(
    date '2024-01-09',
    'FiscalYear|FiscalQuarter|FiscalMonth|FiscalWeek',
    fiscal_period_table => TABLE(my_fiscal_calendar)
);

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.

Related Documentation

• TO_DATE
• TO_TIMESTAMP
• Formatting Functions
• Fiscal Calendar Options
• Date and Time Functions