TO_CHAR function Oracle

TO_CHAR is a conversion function in Oracle to convert

1. number to character

2. date to character



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.


SQL> select to_char(‘012’) as str from dual;



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)

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.


SQL> select sysdate,to_char(sysdate,’mm/dd/yyyy’) as fmt_date from dual;

———      ———-
23-FEB-13  02/23/2013

SQL> select sysdate,to_char(sysdate,’YYTH MONTH YEAR’) as fmt_date from dual;

——— ———————————————————

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;


2. select to_char(sysdate,’mm/dd/yyyy hh:mi:ss AM’) as today from dual;

02/23/2013 04:12:58 PM

3. select to_char(sysdate,’YEAR’) as today from dual;


4. select to_char(sysdate,’mon ddth yyyy’) as today from dual;

feb 23rd 2013


Recommended read:

Julian Date


One thought on “TO_CHAR function Oracle

  1. 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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s