These functions are required
to convert the date from one type to another.
ORACLE
provides following primary data types either to declare column of a table or to
declare a variable in PL-SQL. The data types are
Char, varchar2, number,
date
Depending on the above data types ORACLE provides following conversion functions
TO_NUMBER(),TO_DATE(),TO_CHAR()
This
is a conversion function required to convert date to char or number to char
TO_CHAR(DATE/NUMBER,’FMT’)
DATE TO CHAR:
To convert date to char
ORACLE provides a series of formatting strings are as follows
DY à MON MONTH à JANUARY
DAY à MONDAY YY à 09
DD à 22 YYYY à 2009
DDTH à 22ND HH à 5(HOUR)
MM à 01 MI à 40(MIN)
MON à JAN SS
à 15(SEC)
DDTHSPL à TWENTY-SECOND
Select
to_char(to_date(’22-jan-2008’), ‘day ddth month
yyyy’) from dual;
Ans Tuesday 22nd January
2009
Select
to_char(systimestamp, ‘hh:mi:ss’) from dual;
Ans current time
NUMBER TO CHAR:
To
convert number to char the formatting strings are
$ à to prefix a dollar symbol
9 à represent the number in
char format, th.
, àthousand separator
. à decimal number
L à to prefix local currency
symbol
Select ename, to_char(sal,
‘L99,999.99’) from emp;
TO_NUMBER ():
This
function is required to convert string type of number to actual number
Select to_number(‘1000’)
from dual;
