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:
Great articles for beginners like me. Just started reading one page which made me kept on reading all pages. You have great explanation skills. Please keep on posting all these basics.