TO_CHAR
The TO_CHAR function converts a date or number to a CHAR.
Syntax
TO_CHAR (value[,’template’])
Remarks
| • | The optional template can be of any length, but make sure it contains as many digits as the longest expected input value. |
| • | If two arguments are provided, TO_CHAR treats empty strings as NULL. |
| • | Date templates are the same as those used in FORMAT_DATE. |
| • | Most number template indicators (commas, decimal points, letter designations) can be used in combination. |
| • | The table below illustrates representative effects of number templates. |
|
Template |
Sample Input | Result | Comments |
|
999,999,999 |
12345 |
12,345 |
Returns the input value with commas placed as in the template. |
|
099,999 |
1234 |
001,234 |
Returns leading zeroes to fill out the number of digits in the template. |
|
$99,999 |
1234 |
$1,234 |
Returns the input expressed as a dollar amount, with commas. |
|
$099,999.99 |
1234.56 |
$001,234.56 |
Returns the input expressed as a dollar amount with two decimal places, with leading zeroes to fill out the number of digits in the template. |
|
L999,999 |
12345 |
$12,345 |
Returns the local currency symbol in the specified position. |
|
999,999PR |
-12345 |
<12,345> |
If the input is negative, returns it in angle brackets. |
|
s999,999 |
12345 |
+12,345 |
Returns the input with a leading plus or minus sign. Zero returns +0. |
|
S999,999pr |
-12345 |
<-12,345> |
Leading S and trailing PR can be used together in the template. |
Example
SELECT
TO_CHAR(TIME '17:45:29', 'hh24 HH:MI:SS')
FROM
/services/databases/system/DUAL
This returns:
17 05:45:29