Conversion Function in Oracle

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            à                22^ND                           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;