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

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .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