15. CONVERSION FUNCTIONS

CONVERSION FUNCTIONS

Implicit character to date conversion are possible when the character string conforms to the following date pattern.

[D/DD] SEPERATOR [MON/MONTH] SEPERATOR [R/RR/YY/YYYY].

All arithmetic functions containing a NULL value passed in as input will return number values only.

Sub query may return NULL values but won't retrieve NULL records from the table.

• TO_CHAR;
• TO_DATE;
• TO_NUMBER;
• TO_TIMESTAMP;
• TO_TIMESTAMP_TZ;
• TO_YMINTERVAL;
• TO_DSINTERVAL;
• TO_BINARY_FLOAT;
• TO_BINARY_DOUBLE;
• TO_BLOB;
• TO_CLOB;
• TO_LOB
• BIN_TO_NUM;
• NUMTOYMINTERVAL;
• NUMTODSINTERVAL;
• RAWTOHEX
• TIMESTAMP_TO_SCN
• SCN_TO_TIMESTAMP;
• TO_NCHAR;
• TO_NCLOB;

  TO_CHAR:  Converts date and numbers into character format;

           

           SYN: TO_CHAR (DATE,’FORMAT’);

EG:  SELECT TO_CHAR(SYSDATE,’D’) FROM DUAL;

       SELECT TO_CHAR(SYSDATE,’DD’) FROM DUAL;

       SELECT TO_CHAR(SYSDATE,’DY’) FROM DUAL;

       SELECT TO_CHAR(SYSDATE,’CC’) FROM DUAL;

       SELECT TO_CHAR(SYSDATE,’FM MM/DD/YY’) FROM DUAL;

       SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,’MON’)=’FEB’;

       SELECT * FROM EMP WHEE TO_CHAR(HIREDATE,’MM’)=02;

       SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,’YY’)=81;

       SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,’D’)=1;

       SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,’W’)=1;

   TO_DATE: Converts character format to date format

SYN: TO_DATE (‘C’,’FORMAT’);

EG:   SELECT TO_DATE (‘10’,’DD’) FROM DUAL;

        SELECT TO_DATE (‘10’,’MM’) FROM DUAL;

        SELECT TO_DATE (‘10’,’YY’) FROM DUAL;

        SELECT TO_DATE (‘FEB’,’MON’) FROM DUAL;

        SELECT TO_CHAR (ROUND(TO_DATE(‘11’,’YY’),’YEAR’),’MM’) FROM DUAL;

------------------------------------------------------------------------------------

TO_NUMBER: Converts character  into NUMBER FORMAT

TO_YMINTERVAL: Converts the character into YEAR AND MONTH                                     INTERVAL

TO_DISINTERVAL: Converts the character into DAY,HOUR,MINUTE AND                             SECONDS INTERVAL

BIN_TO_NUM: BINARY TO NUMBER

NUMTOYMINTERVAL: NUMBER TO YEAR AND MONTH INTERVALS

NUMTODSINTERVAL: NUMBER TO DAY ,HOUR,MINUTE AND SECONDS                                   INTERVAL

EG:  

        SELECT TO_TIMESTAMP(‘11’,’FF’) FROM DUAL;

        SELECT TO_TIMESTAMP(‘11’,’HH’) FROM DUAL;

        SELECT TO_TIMESTAMP_TZ(‘05’,’TZH’) FROM DUAL;

        SELECT TO_YMINTERVAL (’10-06’) FROM DUAL;

        SELECT SYSDATE,SYSDATE+TO_YMINTERVAL(’10-06’) FROM DUAL;

        SELECT SYSTIMETAMP, SYSTIMESTAMP+TO_DSINTERVAL (’10 10:10:10’)

                                                                                               FROM DUAL;

         SELECT BIN_TO_NUM(1,1,1) FROM DUAL;

         SELECT NUMTOYMINTERVAL(11,’YEAR’) FROM DUAL;

         SELECT SYSDATE,SYSDATE+NUMTOYMINTERVAL(11,’YEAR’) FROM DUAL;

         SELECT NUMTOYMINTERVAL(11,’MONTH’) FROM DUAL;

         SELECT NUMTOYMINTERVAL(9999,’YEAR’) FROM DUAL;

         SELECT NUMTODSIINTERVAL(10,’DAY’) FROM DUAL;

         SELECT NUMTODSINTERVAL(10,’HOUR’) FROM DUAL;

         SELECT NUMTODSINTERVAL(10,’SECONDS’) FROM DUAL;

         SELECT ORA_ROWSCN FROM DUAL;

         SELECT SCN_TO_TIMESTAMP(ORA-ROWSCN) FROM DUAL;

         SELECT TIMESTAMP_TO_SCN (SCN_TO_TIMESTAMP(ORA_ROWSCN))               FROM DUAL;

         SELECT TO_NUMBER (‘99’) FROM DUAL;

         SELECT  SYSDATE+TO_NUMBER(‘10’) FROM DUAL;

--------------------------------------------------------------------------

NUMBER FORMATS:

$

9 ------------  DIGIT REPRESENTATION

0                        

.  -----------  SPECIFIES THE DECIMAL

,

PR ----------- ENCLOSE THE –VE VALUES IN ANGLE BRACKETS

MI ----------- REPRESENTS THE –VE SIGN

S  ----------- SIGN

L  ----------- LOCAL CURRENT SYMBOL

B ----------- BLANK SPACE

C ----------- CURRENCY CODE

D ----------- DECIMAL POINT

EEEE ------- SPECIFIES THE EXPONENTIAL

G ----------- GROUPING

U

V

X

N

  EG:  SELECT SAL,TO_CHAR(SAL,’$9999’) FROM EMP;

         SELECT SAL,TO_CHAR(SAL,’$9999.99’) FROM EMP;

         SELECT SAL,TO_CHAR(SAL,’$9999D99’) FROM EMP;

         SELECT TO_CHAR(-10,’99PR’) FROM DUAL;

         SELECT TO_CHAR(-10,’99MI’) FROM DUAL;

         SELECT TO_CHAR(10,’99U9’) FROM DUAL;

         SELECT TO_CHAR(10,’L99’) FROM DUAL;

         SELECT TO_CHAR(10,’C99’) FROM DUAL;