1. What can we do with Conversion Functions ?
We can do the following:
(i) Convert one datatype to another datatype.
(ii) There are 3 types of conversion
- converting NUMBER, DATE to CHARACTER. [ TO_CHAR() ]
- converting NUMBER, CHARACTER to DATE. [ TO_DATE() ]
- converting CHARACTER to NUMBER. [ TO_NUMBER() ]
(I) TO_CHAR():
Syntax:
=> TO_CHAR( date [, format] )
=> TO_CHAR( number [, format] )
Examples:
=> TO_CHAR( SYSDATE, 'YYYY' ) = 2012
=> TO_CHAR( SYSDATE, 'MON' ) = FEB
=> TO_CHAR( SYSDATE, 'DD' ) = 13
=> TO_CHAR( SYSDATE, 'DAY' ) = MONDAY
=> TO_CHAR(123456, '9,99,999') = 1,23,456
(II) TO_DATE():
Syntax:
=> TO_DATE( charString [,format] )
=> TO_DATE( number [,format] )
Examples:
=> TO_DATE('12-02-12','DD-MM-YY') = 12-FEB-12
=> TO_DATE('17-FEBRUARY-2012','DD-MONTH-YYYY') = 17-FEB-12
=> TO_DATE(123, 'J') = 03-MAY-12 { '01-JAN-12' + 123 }
(III) TO_NUMBER():
Syntax:
=> TO_NUMBER( charString [,format] )
Examples:
=> TO_NUMBER( '1,000' , '9,999') = 1000
=> TO_NUMBER( '$1,23,456' , 'L9,99,999') = 123456