TO_CHAR (SQL)

expression

A logical date, time, timestamp, or number expression to be converted to a character string according to the format specified by format. If expression is null, TO_CHAR returns null.

format

A character code that specifies a date, timestamp, or number format for the expression conversion.

• If you specify format with an invalid date, time, or timestamp code element (for example, YYYYY, MIN, HH48), TO_CHAR returns the format code literal for the invalid code element. For all other valid code elements, it returns the date, time, or timestamp conversion values.

• If TO_CHAR cannot recognize any format code elements (for example, format is an empty string) or if a number format has fewer digits than the expression value, TO_CHAR returns pound signs (#) in place of the original characters. This is true when expression begins with at least two integer digits. Otherwise, TO_CHAR returns NULL.

These tables list the valid format codes that you can specify for each expression type: date, time, date and time (timestamp), and number.

Separator characters are required between the date format elements, with the exception of the following format strings: YYYYMMDD, DDMMYYYY, and YYYYMM. The last of these returns the year and month values and ignores the day of the month.

Locales mentioned in the format code definitions refer to the same locales described in the ObjectScript $ZDATE and $ZDATEH documentation.

When converting times to strings, format must be a string that contains only the time format codes shown in the table. If format includes any other codes, then TO_CHAR interprets the expression as a date instead.

When converting timestamps to formatted datetime strings, format must be a string containing the date and time format codes shown in the "Date Formats" and "Time Formats" tables. To perform this conversion, expression must be a valid Logical timestamp value.

format can specify the decimal separator and the numeric group separator either as a literal character, or as the current value of the locale’s DecimalSeparator and NumericGroupSeparator. You can determine the current locale values using ObjectScript as follows:

  write ##class(%SYS.NLS.Format).GetFormatItem("DecimalSeparator"),!
  write ##class(%SYS.NLS.Format).GetFormatItem("NumericGroupSeparator")

If format contains fewer integer digits than the input numeric expression, TO_CHAR does not return a number. Instead, it returns a string of two or more pound signs (##). The number of pound signs represents the length of the current format argument, plus one.

If format contains fewer decimal digits than the input numeric expression, TO_CHAR rounds the number to the specified number of decimal digits. If no decimal format is provided, TO_CHAR rounds the number to an integer.

Convert Dates to Formatted Date Strings

This statement uses TO_CHAR to convert $HOROLOG date integers or full $HOROLOG string values to formatted date strings or date and time strings:

SELECT
  TO_CHAR(66256,'YYYY-MM-DD') AS Date2FormattedDate,
  TO_CHAR(66256,'YYYY-MM-DD HH24:MI:SS') AS Date2FormattedDateTime,
  TO_CHAR('66256,50278','YYYY-MM-DD') AS DateTime2FormattedDate,
  TO_CHAR('66256,50278','YYYY-MM-DD HH24:MI:SS') AS DateTime2FormattedDateTime

In this statement, each TO_CHAR call takes a date integer and returns a date string formatted according to the format string argument:

SELECT
  TO_CHAR(66256,'MM/DD/YYYY'),         /* returns 02/22/2018 */
  TO_CHAR(66256,'DAY MONTH DD, YYYY')  /* returns Thursday February 22, 2018 */

This statement converts a date integer to a formatted date string. Invalid format characters are passed through to the output string as literals. It returns the string The date 05/27/2022 should be noted.

SELECT TO_CHAR(66256,'The date MM/DD/YYYY should be noted')

This statement converts date expressions to the day of the year, defined as the number of days elapsed since January 1 of the specified year. To use this syntax, the date expression must be in $HOROLOG format. The time value in the second TO_CHAR call is ignored. In the two TO_CHAR calls, the day-of-year format element (DDD) and the year elements (YYYY and YY) appear in a different order.

SELECT
  TO_CHAR('66235','DDD days into YYYY'),
  TO_CHAR('66235,12345','Year YY: DDD days elapsed')

In this statement, TO_CHAR returns an incorrect date value, because it interprets the separators as minus signs. Therefore, it evaluates the expression as 2022 – 5 – 2 = 2015, which in $HOROLOG integer format corresponds to the date 1846-07–08.

SELECT TO_CHAR(2022-05-02,'YYYY-MM-DD') -- Incorrect usage

Convert Times to Formatted Time Strings

This statement causes '66256' to be interpreted as the time value 06:24:16 PM.

SELECT TO_CHAR('66256','HH12:MI:SS PM')

This statement converts the time portions of two Logical timestamps to formatted time strings. Because format does not support fractional seconds, the fractional seconds in expression are truncated.

SELECT TO_CHAR(SYSDATE,'HH12:MI:SS PM'),
       TO_CHAR(CURRENT_TIMESTAMP(6),'HH12:MI:SS PM')

Convert Timestamps to Formatted Date and Time Strings

This statement returns the current system date (a timestamp), and the current system date converted for display with two different formats:

SELECT
  SYSDATE,
  TO_CHAR(SYSDATE,'MM/DD/YYYY HH:MI:SS'),
  TO_CHAR(SYSDATE,'DD MONTH YYYY at SSSSS seconds')

Any characters used in the format string that are not format codes are returned in place in the resulting string.

Convert Numbers to Formatted Numeric Strings

This statement converts the number 1000 to strings with varying numbers of digit format codes.

• In the first conversion, the number has more digits than specified digit format codes. TO_CHAR returns pound symbols equal to the number of digits in the number.

• In the second conversion, the number has the same number of digits as specified digit format codes. TO_CHAR returns the number in character string form. Because the number is an unsigned positive integer, TO_CHAR prepends a leading zero to the numeric string.

• In the third conversion, the number has fewer digits than specified digit format codes. TO_CHAR returns the number in character string form and prepends two leading zeros: one because the number is an unsigned positive integer one for the extra digit format code.

SELECT
  TO_CHAR(1000,'999'),  -- '####'
  TO_CHAR(1000,'9999'), -- ' 1000'
  TO_CHAR(1000,'99999') -- '  1000'

The statement shows the use of separator characters:

• The first conversion returns the string: ' 1,000.00'.

• The second conversion might return the same value, but the separator characters displayed depend on the locale setting.

SELECT
  TO_CHAR(1000,'9,999.99'),
  TO_CHAR(1000,'9G999D99')

This statement shows the use of positive and negative signs. The leading space appears only before a positive number with no sign formatting. No leading space appears before a negative number or any signed number, regardless of the placement of the sign.

SELECT 
  TO_CHAR(10,'99.99'),   -- ' 10.00'
  TO_CHAR(-10,'99.99'),  -- '-10.00'
  TO_CHAR(10,'S99.99'),  -- '+10.00'
  TO_CHAR(-10,'S99.99'), -- '-10.00'
  TO_CHAR(10,'99.99S'),  -- '10.00+'
  TO_CHAR(-10,'99.99S')  -- '10.00-'

This statement shows the use of the "FM" format to override the default leading blank for unsigned positive numbers:

SELECT 
  TO_CHAR(12345678.90,'99,999,999.99'),  -- ' 12,345,678,90'
  TO_CHAR(12345678.90,'FM99,999,999.99') -- '12,345,678,90'

This statement shows the use of the leading dollar sign. The dollar sign is always preceded either by a sign or by a blank character.

SELECT 
  TO_CHAR(1234567890,'$9G999G999G999'),  -- ' $1,234,567,890'
  TO_CHAR(1234567890,'S$9G999G999G999'), -- '+$1,234,567,890'
  TO_CHAR(12345678.90,'$99G999G999D99')  -- ' $1,234,567,8.90'

The statement shows what happens when the format argument contains fewer decimal (fractional) digits than the input numeric expression. The returned numbers are rounded to 1234567.5 and 1234568, respectively.

SELECT
  TO_CHAR(1234567.4999,'9999999.9'),
  TO_CHAR(1234567.91,'9999999')

Julian Date Conversion

The Julian date format enables you to convert dates before December 31, 1840 to character strings. To use this format, specify the format argument of TO_CHAR as 'J' or 'j'. Using this format, you can convert a date value for data type %DateOpens in a new tab or %TimeStampOpens in a new tab to a seven-digit Julian date integer, with leading zeros added when necessary. For example:

SELECT
  TO_CHAR('1776-07-04','J') AS UnitedStatesStart, --2369916
  TO_CHAR('-0031-09-02','J') AS RomanEmpireStart  --1709980

The returned integer is a count of days from January 1, 4712 BCE. The maximum expression value that you can convert to a Julian date is '9999-12-31' (Julian day count 5373484). The minimum value is '-4712-01-01' (Julian day count 0000001).

By default, the %DateOpens in a new tab data type does not represent dates prior to December 31, 1840. However, you can redefine the MINVAL parameter for this data type to permit representation of earlier dates as negative integers, with the limit of January 1, Year 1. This representation of dates as negative integers is not compatible with the Julian date format described here. For more details, see Data Types.

The Julian day count value of 1721424 returns January 1st of Year 1 (1–01–01) in the Julian calendar. Julian day counts less than this values return BCE dates, which are displayed with the year preceded by a minus sign.

TO_CHAR permits you to return a Julian day count corresponding to a date expression. TO_DATE permits you to return a date expression corresponding to a Julian day count, as shown in this example:

SELECT 
  TO_CHAR('1776-07-04','J') AS JulianCount, -- 2369916
  TO_DATE(2369916,'J') AS JulianDate        -- 1776-07-04

Related SQL Functions

• TO_CHAR converts a date integer, timestamp, or a number to a string.

• TO_DATE performs the reverse operation for dates. It converts a formatted date string to a date integer.

• TO_TIMESTAMP performs the reverse operation for timestamps. It converts a formatted date and time string to a standard timestamp.

• TO_NUMBER performs the reverse operation for numbers. It converts a numeric string to a number.

• CAST and CONVERT perform DATE, TIMESTAMP, and NUMBER data type conversions.