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:

Julian Date