TO_CHAR Function

The TO_CHAR function converts an expression that evaluates to a DATE, DATETIME, or numeric value to a character string.

The returned character string represents the data value that the first argument specifies, using a formatting mask that the second argument defines in a format_string that can include special formatting symbols and literal characters.

  • The first argument to this function must be of a DATE, DATETIME, or built-in numeric data type, or a character string that can be converted to one of these data types. If the value of the initial DATE, DATETIME, or numeric argument is NULL, the function returns a NULL value.
  • The second argument to this function is a character string that specifies a formatting mask. What set of special characters is appropriate for the formatting mask primarily depends on whether the first argument to the TO_CHAR function represents a point in time or a number.

Formatting DATE and DATETIME expressions

The format_string argument does not need to imply the same time units as the value in the first argument to the TO_CHAR function. When the precision implied in the format_string is different from the DATETIME qualifier in the first argument, the TO_CHAR function extends the DATETIME value as if it had called the EXTEND function.

In the following example, the user wants to convert the begin_date column of the tab1 table to a character string. The begin_date column is defined as a DATETIME YEAR TO SECOND data type. The user uses a SELECT statement with the TO_CHAR function to perform this conversion: 
SELECT TO_CHAR(begin_date, '%A %B %d, %Y %R') FROM tab1;

The symbols in the format_string of this example have the following meanings.

Symbol
Meaning
%A
Full weekday name, as defined in the locale
%B
Full month name, as defined in the locale
%d
Day of the month as an integer (01 through 31). A single-digit value is preceded by a zero (0).
%Y
Year as a 4-digit decimal number
%R
Time in 24-hour notation (equivalent to %H:%M format, as defined below).

Note that the comma ( , ) that immediately follows the %d format specification in the example above is a literal character, rather than a separator of arguments to the TO_CHAR function. The second argument is the quoted string '%A %B %d, %Y %R' that defines the formatting mask for representing the first argument in the value that TO_CHAR returns.

Applying this format_string to the begin_date column value returns this result: 
Wednesday July 25, 2013 18:45
The query in the next example calls TO_CHAR to apply the same format string to an ADD_MONTHS expression, and shows the results of the query: 
SELECT ship_date, TO_CHAR(ADD_MONTHS(ship_date, 1), '%A %B %d, %Y')
AS survey_date FROM orders;


ship_date    03/12/2013
survey_date  Thursday April 12, 2013
In the query output above,
  • the ship_date value is formatted according to the DB_DATE environment variable setting,
  • and the survey_date value is formatted according to the '%A %B %d, %Y %R' formatting string argument to the TO_CHAR function.

Additional symbols that are valid in the format_string argument to the TO_CHAR function for DATE or DATETIME values include the following.

Symbol
Meaning
%a
Abbreviated weekday name, as defined in the locale
%b
Abbreviated month name, as defined in the locale
%C
The century number (the year divided by 100 and truncated to an integer) as an integer (00 through 99)
%D
The same as the %m/%d/%y format
%e
Day of the month as a number (1 through 31). A single-digit value is preceded by a blank space.
%Fn
The value of the fraction of a second, with precision specified by the unsigned integer n. The default value of n is 2; the range of n is 0 < n < 5. This value overrides any width or precision that is specified between the % and F characters.
%h
Same as the %b format: abbreviated month name, as defined in the locale
%H
Hour as a 2-digit integer (00 through 23) (24-hour clock)
%I
Hour as a 2-digit integer (00 through 11) (12-hour clock)
%m
Month as an integer (01 through 12). Any single-digit value is preceded by a zero (0).
%M
Minute as a 2-digit integer (00 through 59)
%S
Second as a 2-digit integer (00 through 61). The second value can be up to 61 (instead of 59) to allow for the occasional leap second and double leap second.
%T
Time in the %H:%M:%S format
%w
Weekday as a number (0 through 6); 0 represents the locale equivalent of Sunday.
%y
Year as a 2-digit decimal number.
For example, suppose that on August 23, 2013, the DB-Access facility issued the following query:
SELECT TO_CHAR(CURRENT YEAR TO FRACTION(5), "%Y-%m-%d %H:%M:%S.%F")
FROM sysmaster:sysdual;
In this example, the format string argument specifies a user format with the following literal characters as separators between the DATETIME field values:
  • ASCII 45 ( - ) hyphen to separate the year, month, and day values
  • ASCII 32 ( ) blank to separate the day from the hour
  • ASCII 58 ( : ) colon to separate the hour, minute, and seconds
  • ASCII 46 ( . ) period to separate the second from the fraction of a second.
This is the returned value in the specified DATETIME user format:
(expression)   2013-08-23 13:15:53.00
If you omit the format_string argument when a DATETIME or DATE expression is the first argument, the TO_CHAR function uses as a default the setting of the DBTIME or DBDATE environment variables to format the value represented in the first argument. In nondefault locales, the default format for DATETIME and DATE values is specified by environment variables such as GL_DATETIME and GL_DATE.
Important: For DATETIME user formats whose precision includes both SECOND and FRACTION data values, those fields are concatenated unless a separator character is explicitly defined between the %S and %F formatting directives. In version 11.70.xC7 and earlier HCL OneDB™ releases, the %F directive inserted the ASCII 46 character ( . ) by default between the SECOND and FRACTION field values. In this release, however, the %F directive implies no default separator.

For the order of precedence among the HCL OneDB environment variables that can specify the display and data entry formats for the built-in chronological data types, see the topic Precedence of DATE and DATETIME format specifications.

Formatting numeric and MONEY expressions

The format_string argument to the TO_CHAR function supports the same numeric formatting masks that are used for ESQL functions like rfmtdec( ), rfmtdouble( ), and rfmtlong( ). A detailed description of the HCL OneDB numeric-formatting masks for numeric values (when formatting numeric expressions as strings) is in the HCL OneDB ESQL/C Programmer's Manual. Below is a short summary description of the numeric formatting masks.

A numeric-formatting mask specifies a format to apply to some numeric value when formatting a numeric expression as a string. This mask is a combination of the following formatting characters:

Symbol
Meaning
*
This character fills with asterisks any positions in the display field that would otherwise be blank
&
This character fills with zeros any positions in the display field that would otherwise be blank
#
This character changes leading zeros to blanks. Use this character to specify the maximum leftward extent of a field.
<
This character left-justifies the numbers in the display field. It changes leading zeros to a NULL string.
,
This character indicates the symbol that separates groups of three digits (counting leftward from the units position) in the whole-number part of the value. By default, this symbol is a comma. You can set the symbol with the DBMONEY environment variable. In a formatted number, this symbol appears only if the integer part of the value has four or more digits.
.
This character indicates the symbol that separates the integer part of a money value from the fractional part. By default, this symbol is a period. You can set the symbol with the DBMONEY environment variable. You can have only one period in a format string.
-
This character is a literal. It appears as a minus sign when expr1 is less than zero. When you group several minus ( - ) signs in a row, a single minus sign floats to the rightmost position that it can occupy; it does not interfere with the number and its currency symbol.
+
This character is a literal. It appears as a plus sign when expr1 is greater than or equal to zero, and as a minus sign when expr1 is less than zero. When you group several plus signs in a row, a single plus or minus sign floats to the rightmost position that it can occupy; it does not interfere with the number and its currency symbol.
(
This character is a literal. It appears as a left parenthesis ( ( ) to the left of a negative number. It is one of the pair of accounting parentheses that replace a minus sign for a negative number. When you group several in a row, a single left parenthesis floats to the rightmost position that it can occupy; it does not interfere with the number and its currency symbol.
)
This is one of the pair of accounting parentheses that replace a minus sign for a negative value.
$
This character displays the currency symbol that precedes the numeric value. In the default locale, the currency symbol is the dollar sign ($). You can set a nondefault currency symbol with the DBMONEY environment variable. When you group several dollar signs in a row, a single currency symbol floats to the rightmost position that it can occupy; it does not interfere with the number.

Any other characters in the formatting mask are reproduced literally in the formatted value that the TO_CHAR function returns.

In the next three examples, the value of the d_int column expression argument to the TO_CHAR function is -12344455.

This query specifies no formatting mask in a call to TO_CHAR: 
SELECT TO_CHAR(d_int) FROM tab_numbers;
The following table shows the output of this SELECT statement.
(expression)
-12344455
The following query specifies a monetary format mask: 
SELECT TO_CHAR(d_int, "$*********.**") FROM tab_numbers;
The following table shows the output of this SELECT statement.
(expression)
$12344455.00 
SELECT TO_CHAR(d_int, "-$*********.**") FROM tab_numbers;
The query returns - $12344455.00.
SELECT TO_CHAR(12344455,"-$*********.**") FROM tab_numbers;
The following table shows the output of this SELECT statement.
(constant)
$12344455.00

The currency ( $ ) symbol from the formatting mask argument is applied, but the minus ( - ) symbol has no effect, because the value of the first argument is greater than zero.

Note that the TO_CHAR function is a time expression only when its first argument is a DATE or DATETIME expression, or is a character string that can be formatted as a DATE or DATETIME expression. When a numeric or monetary value is its first argument, however, TO_CHAR returns a representation of the value of that argument as a character string, but it does not return a time expression.