to_char function
to_char function converts a number or date to a string.
The syntax for the to_char function is:
to_char( value, [ format_mask ], [ nls_language ] )| Parameter | Description |
|---|---|
| value | a number or date that will be converted to a string. |
| format_mask | optional. format to be used to convert value to a string. |
| nls_language | optional. the nls language used to convert value to a string. |
The structure format depends on whether x is a number or date.
The number format string:
| Parameter | Description |
|---|---|
| 9 | Returns digits in specified positions, with a leading negative sign if negative. |
| 0 | 0999: Returns a number with leading zeros. 9990: Returns a number with trailing zeros. |
| . | Returns a decimal point in the specified position. |
| , | Returns a comma in the specified position. |
| $ | Returns a leading dollar sign. |
| B | If the integer part of a fixed point number is zero, returns spaces for the zeros. |
| C | Returns the ISO currency symbol. The symbol is from the NLS_ISO_CURRENCY database parameter. |
| D | Returns the decimal point symbol. The symbol is from the NLS_NUMERIC_CHARACTER database parameter. The default is a period character. |
| EEEE | Returns the number in the scientific notation. |
| FM | Removes leading and trailing spaces from the number. |
| G | Returns the group separator symbol. The symbol is from the NLS_NUMERIC_CHARACTER database parameter. |
| L | Returns the local currency symbol. The symbol is from the NLS_CURRENCY database parameter. |
| MI | Returns a negative number with a trailing minus sign. Returns a positive number with a trailing space. |
| PR | Returns a negative number in angle brackets(<>). Returns a positive number with leading and trailing spaces. |
| RN | Returns the number as Roman numerals in uppercase, and the number must be an integer between 1 and 3999. |
| rn | Returns the number as Roman numerals in lowercase, and the number must be an integer between 1 and 3999. |
| S | S999 returns a negative number with a leading negative sign; returns a positive number with a leading positive sign. 999S returns a negative number with a trailing negative sign; returns a positive number with a trailing positive sign. |
| TM | Returns the number using the minimum number of characters. The default is TM9. |
| U | Returns the dual currency symbol in the specified position. The symbol is set by NLS_DUAL_CURRENCY database parameter. |
| V | Returns the number multiplied by 10x where x is the number of 9 characters after the V. If necessary, the number is rounded. |
| X | Returns the number in hexadecimal. If the number is not an integer, the number is rounded to an integer. |
| TO_CHAR() | Output |
|---|---|
| TO_CHAR(12345.67, '99999.99') | 12345.67 |
| TO_CHAR(12345.67, '99,999.99') | 12,345.67 |
| TO_CHAR(-12345.67, '99,999.99') | -12,345.67 |
| TO_CHAR(12345.67, '099,999.99') | 012,345.67 |
| TO_CHAR(12345.67, '99,999.9900') | 12,345.6700 |
| TO_CHAR(12345.67, '$99,999.99') | $12,345.67 |
| TO_CHAR(0.67, 'B9.99') | .67 |
| TO_CHAR(12345.67, 'C99,999.99') | USD12,345.67 |
| TO_CHAR(12345.67, '99999D99') | 12345.67 |
| TO_CHAR(12345.67, '99999.99EEEE') | 1.23E+04 |
| TO_CHAR(0012345.6700, 'FM99999.99') | 12345.67 |
| TO_CHAR(12345.67, '99999G99') | 123,46 |
| TO_CHAR(12345.67, 'L99,999.99') | $12,345.67 |
| TO_CHAR(-12345.67, '99,999.99MI') | 12,345.67 |
| TO_CHAR(-12345.67, '99,999.99PR') | 12,345.67 |
| TO_CHAR(2007, 'RN') | MMVII |
| TO_CHAR(12345.67, 'TM') | 12345.67 |
| TO_CHAR(12345.67, 'U99,999.99') | $12,345.67 |
| TO_CHAR(12345.67, '99999V99') | 1234567 |
Convert numbers to string and do the format:
SQL> SELECT TO_CHAR(12345.67) FROM dual;
TO_CHAR(
--------
12345.67
SQL> select to_char(1234.56, '9999.9') from dual;
TO_CHAR
-------
1234.6
SQL> select to_char(1234.56, '9,999.99') from dual;
TO_CHAR(1
---------
1,234.56
SQL> SELECT TO_CHAR(12345.67, '99,999.99') FROM dual;
TO_CHAR(12
----------
12,345.67
SQL> select to_char(1234.56, '$9,999.00') from dual;
TO_CHAR(12
----------
$1,234.56
SQL> select to_char(123, '000099') from dual;
TO_CHAR
-------
000123
SQL>
TO_CHAR() returns # if the number has too many digits for the format. For example:
SQL> SELECT TO_CHAR(12345678.90, '99,999.99') FROM dual;
TO_CHAR(12
----------
##########
SQL>
Format parameters for converting a date value to string.
| Parameter | Description |
|---|---|
| CC | Two-digit century. |
| SCC | Two-digit century with a negative sign (-) for B.C. |
| Q | One-digit quarter of the year. (1, 2, 3, 4; JAN-MAR = 1) |
| YYYY | All 4 digits of the year. |
| IYYY | All 4 digits of the ISO year. |
| RRRR | All 4 digits of the rounded year (governed by the present year). |
| SYYYY | All 4 digits of the year with a negative sign (-) for B.C. |
| Y,YYY | All 4 digits of the year, with a comma after the first digit. |
| YYY | Last 3 digits of the year. |
| IYY | Last 3 digits of the ISO year. |
| YY | Last 2 digits of the year. |
| IY | Last 2 digits of the ISO year. |
| RR | Last two digits of the rounded year, which depend on the present year. |
| Y | Last digit of the year. |
| I | Last digit of the ISO year. |
| YEAR | Name of the year in uppercase. |
| Year | Name of the year with the first letter in uppercase. |
| MM | Two-digit month of the year. (01-12; JAN = 01) |
| MONTH | Full name of the month in uppercase, right-padded with spaces to a total length of nine characters. |
| Month | Full name of the month with first letter in uppercase, right-padded with spaces to a total length of nine characters. |
| MON | First three letters of the name of the month in uppercase. |
| Mon | First three letters of the name of the month with the first letter in uppercase. |
| RM | Roman numeral month. (I-XII; JAN = I) |
| WW | Two-digit week of the year. (1-53) |
| IW | Two-digit ISO week of the year.(1-52 or 1-53) |
| W | One-digit week of the month.(1-5) |
| DDD | Three-digit day of the year. (1-366) |
| DD | Two-digit day of the month.(1-31) |
| D | One-digit day of the week.(1-7) |
| DAY | Full name of the day in uppercase. |
| Day | Full name of the day with the first letter in uppercase. |
| DY | First three letters of the name of the day in uppercase. |
| Dy | First three letters of the name of the day with the first letter in uppercase. |
| J | Julian day-the number of days that have passed since January 1, 4713 B.C. |
| HH24 | Two-digit hour in 24-hour format. (0-23) |
| HH12 | Hour of day.(1-12) |
| HH | Two-digit hour in 12-hour format.(1-12) |
| MI | Two-digit minute. (0-59) |
| SS | Two-digit second.(0-59) |
| FF[1..9] | Fractional seconds. |
| SSSSS | Number of seconds past 12 A.M. (0-86399) |
| MS | Millisecond (millionths of a second). |
| CS | Centisecond (hundredths of a second). |
| -/,.;:"text" | Characters that allow you to separate the aspects of a date and time. |
| AM or PM A.M. or P.M. AD or BC | AM or PM A.M. or P.M. AD or BC |
| SP | Number is spelled out |
| SPTH | Combination of SP and TH |
| EE | Full era name |
| E | Abbreviated era name |
| TZH | Time zone hour. |
| TZM | Time zone minute. |
| TZR | Time zone region. |
| TZD | Time zone with daylight savings information. |
The following table illustrates some TO_CHAR date formatting:
| Format | Output |
|---|---|
| dd Month yyyy | 05 March 2012 |
| dd month YY | 05 march 12 |
| dd Mon | 05 Mar |
| dd RM yyyy | 05 III 2012 |
| Day Mon yyyy | Sunday Mar 2012 |
| Day fmMonth dd, yyyy | Sunday March 5, 2012 |
| Mon ddsp yyyy | Mar five 2012 |
| ddMon yy hh24:mi:ss | 05Mar 12 00:00:00 |
| MONTH DD, YYYY | FEBRUARY 05, 2012 |
| MM/DD/YYYY | 02/05/2012 |
| MM-DD-YYYY | 02-05-2012 |
| DD/MM/YYYY | 05/02/2012 |
| DAY MON, YY AD | MONDAY FEB, 12 AD |
| DDSPTH "of" MONTH, YEAR A.D. | FIFTH of FEBRUARY, TWENTY TWELVE A.D. |
| CC, SCC | 20, 20 |
| Q | 1 |
| YYYY | 2012 |
| IYYY | 2012 |
| RRRR | 2012 |
| SYYYY | 2012 |
| Y,YYY | 2,012 |
| YYY | 012 |
| IYY | 012 |
| YY | 12 |
| IY | 12 |
| RR | 12 |
| Y | 2 |
| I | 2 |
| YEAR | TWENTY TWELVE |
| Year | Twenty Twelve |
| MM | 02 |
| MONTH | FEBRUARY |
| Month | February |
| MON | FEB |
| Mon | Feb |
| RM | II |
| WW | 02 |
| IW | 02 |
| W | 2 |
| DDD | 035 |
| DD | 06 |
| DAY | MONDAY, |
| Day | Monday |
| DY | MON |
| Dy | Mon |
| J | 2439999 |
| ddTH | 05th |
| DDTH | 05TH |
| ddSP | five |
| DDSP | FIVE |
| DDSPTH | FIFTH |
| HH24:MI:SS | 19:12:36 |
| HH.MI.SS AM | 7.12.36 PM |
SQL> select to_char(sysdate, 'Month DD, YYYY') FROM dual;
TO_CHAR(SYSDATE,'M
------------------
July 09, 2012
SQL> select to_char(sysdate, 'yyyy/mm/dd') FROM dual;
TO_CHAR(SY
----------
2012/07/09
SQL>
format_mask beginning with "FM" means that zeros and blanks are suppressed.
SQL> select to_char(sysdate, 'FMMonth DD, YYYY') FROM dual;
TO_CHAR(SYSDATE,'F
------------------
July 9, 2012
SQL> select to_char(sysdate, 'Month DD, YYYY') FROM dual;
TO_CHAR(SYSDATE,'M
------------------
July 09, 2012
SQL>
Adding th to the day and month:
SQL> select to_char(sysdate, 'Month DD, YYYY') FROM dual;
TO_CHAR(SYSDATE,'M
------------------
July 03, 2011
SQL> select to_char(sysdate, 'MON DDth, YYYY') FROM dual;
TO_CHAR(SYSDAT
--------------
JUL 03RD, 2011
SQL> select to_char(sysdate, 'Mon ddth, YYYY') FROM dual;
TO_CHAR(SYSDAT
--------------
Jul 03rd, 2011
SQL> SELECT TO_CHAR(SYSDATE, 'MONTH DD, YYYY, HH24:MI:SS') FROM dual;
TO_CHAR(SYSDATE,'MONTHDD,YYY
----------------------------
JULY 03, 2011, 12:59:20
SQL>
Home »
Oracle »
Conversion Functions »
Oracle »
Conversion Functions »
to_char:
- to_char function
- Format salary with to_char
- Order by the numeric value of days using to_char
- to_char with time format
Related: