TO_CHAR is a conversion function in Oracle to convert
1. number to character
2. date to character
Syntax:-
TO_CHAR(value,[format],[nls_language])
Where both format and nls_language are optional. Oracle distinguishes each function by its arguments.
1. TO_CHAR number
TO_CHAR number function is handy for formatting and constructing fixed length outputs.
Examples:-
SQL> select to_char(‘012’) as str from dual;
STR
—
012
| Query | Result |
| to_char(‘012’) | 012 |
| to_char(‘012′,’9999’) | 12 |
| to_char(‘012′,’$9999.99’) | $12.00 |
| to_char(‘012′,’$0009999.99’) | $0000012.00 |
| to_char(‘012.48′,’$9999.999’) | $12.480 |
TO_CHAR helps to avoid implicit conversion in some scenarios.
See below examples
SQL> Desc t
Name Type
—- ———–
NO NUMBER(38)
CODE VARCHAR2(2)
SQL> select * from t;
NO CODE
—————–
1 11
2 22
3 33
SQL> select * from t where code = 11;
NO CODE
—————–
1 11
SQL> select * from t where code = to_char(11);
NO CODE
—————–
1 11
First query forces oracle to do implicit conversion ( Number 11 will be converted to ’11’) while second query runs without any implicit conversion. Make both side same data type.
Consider TO CHAR example in PL/SQL
SQL> Create or replace procedure proc(p_code number)
2 is
3 v_no number;
4 begin
5 select no into v_no from t where code = to_char(p_code);
6 dbms_output.put_line(‘No is —>’ || v_no);
7 exception
8 when others then
9 dbms_output.put_line(sqlerrm);
10 end;
11 /
Procedure created.
SQL> exec proc(11);
No is —>1
PL/SQL procedure successfully completed.
For large queries implicit conversion affect the performance. So it is better always keep in mind to avoid implicit conversion as much as possible.
2. TO_CHAR date
TO_CHAR date function is one of the most used functions for date conversions and formatting.
Examples:-
SQL> select sysdate,to_char(sysdate,’mm/dd/yyyy’) as fmt_date from dual;
SYSDATE FMT_DATE
——— ———-
23-FEB-13 02/23/2013
SQL> select sysdate,to_char(sysdate,’YYTH MONTH YEAR’) as fmt_date from dual;
SYSDATE FMT_DATE
——— ———————————————————
23-FEB-13 13TH FEBRUARY TWENTY THIRTEEN
Below listed most used date formats used with TO_CHAR date function. ( I have used sysdate for example and the day I tested the examples is February 23, 2013 at 4:00 pm PST )
| Format | Example | Result | Explanation |
| YYYY | to_char(sysdate,’YYYY’) | 2013 | Year |
| YYY | to_char(sysdate,’YYY’) | 13 | Last three digit of the year |
| YY | to_char(sysdate,’YY’) | 13 | Last two digit of the year |
| YEAR | to_char(sysdate,’YEAR’) | TWENTY THIRTEEN | Year in words |
| RR | to_char(sysdate,’RR’) | 13 | Last two digit of the year |
| RRRR | to_char(sysdate,’RRRR’) | 2013 | Year |
| SYYYY | to_char(sysdate,’SYYYY’) | 2013 | S prefixed (–) sign for BC |
| Y,YYY | to_char(sysdate,’Y,YYY’) | 2013 | Year with comma |
| MONTH | to_char(sysdate,’MONTH’) | FEBRUARY | Complete month |
| MON | to_char(sysdate,’MON’) | FEB | 3 letter month format |
| MM | to_char(sysdate,’MM’) | 02 | Month of the year |
| W | to_char(sysdate,’W’) | 4 | Week of the current month |
| WW | to_char(sysdate,’WW’) | 8 | Week of the year (1 – 53) |
| DAY | to_char(sysdate,’DAY’) | SATURDAY | Name of the day |
| DD | to_char(sysdate,’DD’) | 23 | Day in number format |
| D | to_char(sysdate,’D’) | 7 | Day of the week (1 – 7) |
| DDD | to_char(sysdate,’DDD’) | 54 | Day of the year (1 – 366) |
| DY | to_char(sysdate,’DY’) | SAT | Short form of Day |
| HH | to_char(sysdate,’HH’) | 04 | Hour (1 – 12) |
| HH12 | to_char(sysdate,’HH12′) | 04 | Hour in 12 hour format |
| HH24 | to_char(sysdate,’HH24′) | 16 | Hour in 24 hour format |
| MI | to_char(sysdate,’MI’) | 02 | Minute (0 – 59) |
| SS | to_char(sysdate,’SS’) | 23 | Second (0 – 59) |
| SSSSS | to_char(sysdate,’SSSSS’) | 57683 | Seconds after midnight (0 – 86399) |
| PM | to_char(sysdate,’PM’) | PM | AM or PM |
| AM | to_char(sysdate,’AM’) | PM | AM or PM |
| BC | to_char(sysdate,’BC’) | AD | AD or BC |
| CC | to_char(sysdate,’CC’) | 21 | Century |
| DL | to_char(sysdate,’DL’) | Saturday, February 23, 2013 | Date in Long Format |
| J | to_char(sysdate,’J’) | 2456347 | Julian date format |
| Q | to_char(sysdate,’Q’) | 1 | Quarter of the Year (1 – 4) |
Some more examples
1. select to_char(sysdate,’mm/dd/yyyy’) as today from dual;
TODAY
———-
02/23/2013
2. select to_char(sysdate,’mm/dd/yyyy hh:mi:ss AM’) as today from dual;
TODAY
———————-
02/23/2013 04:12:58 PM
3. select to_char(sysdate,’YEAR’) as today from dual;
TODAY
——————————————
TWENTY THIRTEEN
4. select to_char(sysdate,’mon ddth yyyy’) as today from dual;
TODAY
————-
feb 23rd 2013
Recommended read:
