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.

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

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.

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.

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

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

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

1SELECT 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:

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

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.

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

Returns '15:02:12'.

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

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

Returns '5th March 2024'.

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

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

Returns 'FY 2024 Quarter 1 January Week 2'.

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

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

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.

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

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.

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