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