Oracle Explicit Data Type Conversion

Oracle provides three functions to covert from one data type to another.

1. To_CHAR ( number | date, [fmt], [nlsparams] )

The TO_CHAR function converts the number or date to VARCHAR2 data type in the specified format (fmt). The nlsparams parameter is used for number conversions. The nlsparams specifies the following number format elements:

·                     Decimal character

·                     Group separator

·                     Local currency symbol

·                     International currency symbol

If the parameters are omitted, then it uses the default formats specified in the session.

Converting Dates to Character Type Examples

The Date format models are:

·                     YYYY: Four digit representation of year

·                     YEAR: Year spelled out

·                     MM: Two digit value of month

·                     MONTH: Full name of month

·                     MON: Three letter representation of month

·                     DY: Three letter representation of the day of the week

·                     DAY: Full name of the day

·                     DD: Numeric day of the month

·                     fm: used to remove any padded blanks or leading zeros.

SELECT TO_CHAR(hire_date, 'DD-MON-YYYY') FROM EMPLOYEES;

SELECT TO_CHAR(hire_date, 'fmYYYY') FROM EMPLOYEES;

SELECT TO_CHAR(hire_date, 'MON') FROM EMPLOYEES;

SELECT TO_CHAR(hire_date, 'YYYY/MM/DD') FROM EMPLOYEES;

Converting Numbers to Character type Examples

The Number format models are:

·                     9: Specifies numeric position. The number of 9's determine the display width.

·                     0: Specifies leading zeros.

·                     $: Floating dollar sign

·                     Decimal position

·                     Comma position in the number

SELECT TO_CHAR(price, '$99,999') FROM SALES;

SELECT TO_CHAR(price, '99.99') FROM SALES;

SELECT TO_CHAR(price, '99,00') FROM SALES;

2. TO_NUMBER( char, ['fmt'] )

The TO_NUMBER function converts the characters to a number format.

SELECT TO_NUMBER('1028','9999') FROM DUAL;

SELECT TO_NUMBER('12,345','99,999') FROM DUAL;

3. TO_DATE( char, ['fmt'] )

The TO_DATE function converts the characters to a date data type.

SELECT TO_DATE('01-JAN-1985','DD-MON-YYYY') FROM DUAL;

SELECT TO_DATE('01-03-85','DD-MM-RR') FROM DUAL;