TO_CHAR function Oracle

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

 

About these ads

About sqlandplsql

This blog is to help the community to learn more about the Oracle, PL/SQL, Ubuntu, MySQL etc and few general topics. An attempt to explain all topics in a simple and elegant manner. Suggestions, comments, feedbacks and referrals are highly appreciated.
This entry was posted in Oracle, PL/SQL and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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