TO_CHAR (SQL)
Synopsis
TO_CHAR(expression,format)
TO_CHAR(expression)
TOCHAR(...)
Description
-
TO_CHAR(expression,format) converts a date, time, timestamp (date and time), or number expression to a character string according to the specified format string.
This statement converts the current date to the format 'MONTH DD, YYYY', where MONTH is the full month name, DD is the two-digit day of the month, and YYYY is the four-digit year.
SELECT TO_CHAR(CURRENT_DATE,'MONTH DD, YYYY')
Examples:
TO_CHAR(expression) converts a date, time, timestamp, or number expression according to the default Logical mode format for the expression type.
-
Date expressions and time expressions convert to the InterSystems SQL Logical $HOROLOG format, which is a string of two comma-separated integers that represent a date and time. The first integer is the number of days since December 31, 1840. The second integer is the number of seconds since midnight of the current day.
-
Timestamp expressions convert to the format YYYY-MM-DD HH:MI:SS.
-
Number expressions convert to integers. Any leading zeros or plus signs are removed, and the number is truncated at the first nonnumeric character, such as a comma or period.
This statement converts the current date and time, represented as a timestamp, to a character string of the format YYYY-MM-DD HH:MI:SS.
SELECT TO_CHAR(CURRENT_TIMESTAMP)
-
TOCHAR(...) is equivalent to TO_CHAR(...).
Arguments
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.
Date Expressions
To convert date expressions, expression must be an integer or string in $HOROLOG format.
If expression is an invalid date, (for example, February 30), InterSystems IRIS® issues an SQLCODE -400 error.
If expression represents a date before 12/31/1840, then to convert the date, you must use the Julian date format (format argument = 'J'). For more details, see Julian Date Conversion.
Time Expressions
To convert time expressions, expression must be in one of these formats:
-
A $HOROLOG time integer (the time component of $HOROLOG), where expression is a valid Logical time integer in the range 0 through 86399. Do not supply a full $HOROLOG value with both date and time components (such as 64701,42152). TO_CHAR time conversion converts only the first component of $HOROLOG, the date component, to a formatted time string and ignores the second component, the time component.
-
A Logical timestamp value. The value for expression must be of the %TimeStampOpens in a new tab data type (not a string data type) in the format YYYY-MM-DD hh:mm:ss. If you specify only a time format in format, then TO_CHAR ignores the date component of the timestamp converts only the time component. For example, SYSDATE is a Logical timestamp.
-
A time value in standard ODBC time format. The value for expression must be in the format hh:mm:ss and can be a string.
-
A time value in local time format using the current NLS locale settings. For example, if the NLS TimeSeparator is set to “^”, the value for expression can be in the format hh^mm^ss and can be a string.
If expression is an invalid time, (for example, 6:61 P.M.), InterSystems IRIS issues an SQLCODE -400 error.
Timestamp Expressions
To convert timestamp expressions, expression must be of the format YYYY-MM-DD HH:MI:SS, or one of the following valid variants:
-
For month and date values less than 10, leading zeros are optional. If the leading zero is omitted, it is also omitted in the returned date.
-
The seconds value can be omitted, but you must specify the colon indicating its place (for example, HH:MI:). In the returned time, the seconds default to 00.
-
The seconds value can include fractional seconds (for example, HH:MI:SS.fff). In the returned time, these fractional seconds are truncated.
-
A timestamp must include a time portion, even if format does not specify time formatting.
If expression is not a valid timestamp format, TO_CHAR interprets it as an integer, ending interpretation when it encounters the first non-integer character.
If format is a date or timestamp format, TO_CHAR interprets expression as a $HOROLOG date integer. Thus 2010-03-23 12-15:23 (note erroneous hyphen in time value) is interpreted as the $HOROLOG date 2010 (1846-07-03 12:00:00 AM).
If expression is an invalid date or time, (for example, February 30 or 6:61 P.M.), InterSystems IRIS issues an SQLCODE -400 error.
Number Expressions
To convert number expressions, expression must be a numeric data type or a numeric string. TO_CHAR truncates strings at the first nonnumeric integer. It interprets a string with no leading numeric values as 0.
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.
Format Code | Meaning |
---|---|
D | Day of week (1–7). By default, 1 is Sunday (the first day of the week), but you can configure this value. For more details, see DAYOFWEEK. |
DD | Two-digit day of month (01–31). |
DY |
Abbreviated name of day, as specified by the WeekdayAbbr property in the current locale. Defaults: Sun Mon Tue Wed Thu Fri Sat |
DAY |
Name of day, as specified by the WeekdayName property in the current locale. Defaults: Sunday Monday Tuesday Wednesday Thursday Friday Saturday |
MM | Two-digit month number (01–12; 01 = JAN). |
MON |
Abbreviated name of month, as specified by the MonthAbbr property in the current locale. Defaults (case-insensitive): Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec |
MONTH |
Full name of the month, as specified by the MonthName property in the current locale. Defaults (case-insenitive) January February March April May June July August September October November December |
YYYY | Four-digit year. |
YYY | Last 3 digits of the year. |
YY | Last 2 digits of the year. |
Y | Last digit of the year. |
RRRR | Four-digit year. |
RR | Last 2 digits of the year. |
DDD | Day of the year (number of days since January 1 of specified year) |
J | Julian date (number of days since January 1, 4712 BCE). For more details, see Julian Date Conversion. |
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.
Format Code | Meaning |
---|---|
HH | Hour of day (1–12) |
HH12 | Hour of day (1–12) |
HH24 | Hour of day (0–23) |
MI | Minute (0– 59) |
SS | Second (0–59) |
SSSSS | Seconds since midnight (0–86388) |
AM / PM | Meridian Indicator (AM = before noon, PM = after noon). Converts a time value to 12-hour format with the appropriate AM or PM suffix. The returned AM or PM suffix is derived from the time value, not from the format code you specified. In format, you can use either AM or PM. They are functionally identical. |
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 Code | Description | Example |
---|---|---|
9 |
Return value with the specified number of digits.
|
9999 |
0 |
Return leading or trailing zeros. |
09999 99990 |
$ | Return value with a leading dollar sign. For positive numbers, the dollar sign is preceded by a blank space. | $9999 |
B | Return blanks for the integer part of a fixed-point number when the integer part is zero (regardless of 0 in the format argument). | B9999 |
S |
Return value with a leading or trailing plus sign "+" if positive and a leading or trailing minus sign "-" if negative. |
S9999 9999S |
D | Return a decimal separator character in the specified position. The DecimalSeparator used is the one defined for the locale. The default is a period ".". Only one "D" is allowed in the format argument. | 99D99 |
G | Return a numeric group separator character in the specified positions. The NumericGroupSeparator used is the one defined for the locale. The default is a comma ",". No numeric group separators can appear to the right of the decimal separator. | 9G999 |
FM | Return a value with no leading or trailing blanks. | FM90.9 |
, | Return a comma in the specified position. No comma can appear to the right of the decimal. The format argument cannot begin with a comma. | 9,999 |
. | Return a decimal point (that is, a period ".") in the specified position. Only one "." is allowed in the format argument. | 99.99 |
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.
Examples
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')
More About
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