TO_CHAR

The function converts expr of various data types into STRING.

TO_CHAR(TIMESTAMP)

The function converts expr of data type TIMESTAMP into a STRING value.

Syntax

TO_CHAR(expr,[fmt])

Arguments

expr: Date or time argument.

---

fmt (optional): Date or time format. More on supported formats in the description of the TO_DATE function.

Return Value

The return value is STRING.

Usage Notes

Without optional argument fmt:

• The default timestamp format is applied to convert TIMESTAMP values.

TO_CHAR(NUMBER)

The function converts numerical values in expr into a STRING value.

Syntax

TO_CHAR(expr,[fmt])

Arguments

expr: A NUMBER value.

---

fmt (optional): Format template (see examples).

Element	Example	Description
, (Comma)	9,999	Returns a comma at the specified position. Multiple commas can be specified in a number format model. Restrictions: - A comma element cannot begin a number format model. - A comma cannot appear to the right of a decimal point in a number format model.
. (Dot)	99.99	Returns a decimal point at the specified position. Restriction: Only one decimal point can be specified in a number format model.
$	$9999	Returns a value with a leading dollar sign.
0	0999 9990	Returns leading zeros. Returns trailing zeros.
9	9999	Returns a value with the specified number of digits, with a leading space if positive, or a leading minus if negative. Leading `NULL`s are blank, except for a `NULL` value, which returns a `NULL` for the integer part of the fixed-point number.
B	B9999	Returns blanks for the integer part of a fixed-point number when the integer part is `NULL` (regardless of zeros in the format model).
C	C999	Returns the ISO currency symbol at the specified position.
D	99D99	Returns the decimal character at the specified position. The default is a dot (.). Restriction: Only one decimal character can be specified in a number format model.
EEEE	9.9EEEE	Returns a value in scientific notation.
G	9G999	Returns the group separator at the specified position. Multiple group separators can be specified in a number format model. Restriction: A group separator cannot appear to the right of a decimal point in a number format model.
L	L999	Returns the local currency symbol at the specified position.
MI	9999MI	Returns a negative value with a trailing minus sign (-). Returns a positive value with a trailing space. Restriction: The MI format element can only appear in the last position of a number format model.
PR	9999PR	Returns a negative value in angle brackets. Returns a positive value with leading and trailing spaces. Restriction: The PR format element can only appear in the last position of a number format model.
RN rn	RN rn	Returns a value as Roman numerals in uppercase. Returns a value as Roman numerals in lowercase. The value can be an integer between 1 and 3999.
S	S9999 9999S	Returns a negative value with a leading minus sign (-). Returns a positive value with a leading plus sign (+). Returns a negative value with a trailing minus sign (-). Returns a positive value with a trailing plus sign (+). Restriction: The S format element can only appear in the first or last position of a number format model.
TM	TM	Text minimum number format model returns (in decimal output) the smallest possible number of characters. This element is not case-sensitive. The default is TM9, which returns the number in fixed notation unless the output exceeds 64 characters. If the output exceeds 64 characters, the database automatically returns the number in scientific notation. Restrictions: - No other element can precede this element. - Only a 9 or an E (or e) can follow this element, but no combination of these. The following statement, for example, results in an error: SELECT TO_CHAR(1223, 'TM9e');
U	U9999	Returns the Euro (or other) dual currency symbol at the specified position.
V	999V99	Returns a value multiplied by 10^n (and rounded if necessary), where n is the number of 9's after the V.
X	XXXX xxxx	Returns the hexadecimal value of the specified number of digits. If the specified number is not an integer, the database rounds it to an integer. - This element accepts only positive values or 0. Negative values return an error. - Only 0 (which returns leading zeros) or FM can precede this element. Other elements result in an error. If neither 0 nor FM is specified with X, the return always has a leading space.

Input	Format	Result
-1234567890	9999999999S	'1234567890-'
0	99.99	' .00'
+0.1	99.99	' .10'
-0.2	99.99	' -.20'
0	90.99	' 0.00'
+0.1	90.99	' 0.10'
-0.2	90.99	' -0.20'
0	9999	' 0'
1	9999	' 1'
0	B9999	' '
1	B9999	' 1'
0	B90.99	' '
123456	999999	' 123.456'
-123456	999999	'-123.456'
123456	FM999.009	'123.456'
123456	9.9EEEE	' 1.2E+02'
1E+123	9.9EEEE	' 1.0E+123'
123456	FM9.9EEEE	'1.2E+02'
+123.45	FM999.009	'123.45'
+123.0	FM999.009	'123.00'
+123.45	L999.99	' $123.45'
+123.45	FML999.99	'$123.45'
1234567890	9999999999S	'1234567890+'

Return Value

The return value is STRING.

Usage Notes

If expr is negative, the sign is applied after formatting. Example: TO_CHAR(-1, '€5') results in '-$1' and not '$-1'.

Example 1 | TO_CHAR(CHARACTER)

SELECT to_char('01101');

+------------------+
| TO_CHAR('01101') |
+------------------+
| '01101'          |
+------------------+

Example 2 | TO_CHAR(TIMESTAMP)

WITH my_dat AS (  
  SELECT date'2023-12-31' as expr  union  
  SELECT date'2024-01-01' as expr  union  
  SELECT date'2024-02-01' as expr  
)  
SELECT expr "original date", 
       to_char(expr, 'dd-mm-yyyy') "dd-mm-yyyy",  
       to_char(expr, 'hh24:mi') "time in 24-hr format",  
       to_char(expr, 'iw-iyyy') "iso  week of year and year" 
FROM my_dat;

+--------------------------+------------+----------------------+----------------------------+
| original date            | dd-mm-yyyy | time in 24-hr format | iso  week of year and year |
+--------------------------+------------+----------------------+----------------------------+
| 2023-12-30T22:00:00.000Z | 31-12-2023 | 00:00                | 52-2023                    |
| 2023-12-31T22:00:00.000Z | 01-01-2024 | 00:00                | 01-2024                    |
| 2024-01-31T22:00:00.000Z | 01-02-2024 | 00:00                | 05-2024                    |
+--------------------------+------------+----------------------+----------------------------+

Example 3 | TO_CHAR(NUMBER)

WITH my_dat AS (  
  SELECT 42 col1 union  
  SELECT 7.99 col1 union  
  SELECT .23 col1 union  
  SELECT 1000000 col1 
)  
SELECT col1 "number",  
       to_char(col1, '9,999,999.99') "with commas", 
       to_char(col1, '0,000,000.000') "zero padded",  
       to_char(col1, '$9,999,990.00') "monetary"
FROM my_dat;

+---------+--------------+---------------+---------------+
| number  | with commas  | zero padded   | monetary      |
+---------+--------------+---------------+---------------+
| 0.23    | .23          | 0,000,000.230 | $0.23         |
| 7.99    | 7.99         | 0,000,007.990 | $7.99         |
| 42      | 42.00        | 0,000,042.000 | $42.00        |
| 1000000 | 1,000,000.00 | 1,000,000.000 | $1,000,000.00 |
+---------+--------------+---------------+---------------+