SQL Tutorial : DRL / DQL - CONVERSION

SQL Tutorial : DRL / DQL - CONVERSION

Let's learn about next topic: CONVERSION

CONVERSION:

*************

  

IMPLICIT:

eg:

- SELECT  20+'30'

FROM DUAL

- SELECT LAST_NAME,SALARY,HIRE_DATE

FROM EMPLOYEES
WHERE HIRE_DATE ='07-JUN-94'

EXPLICIT

- TO_CHAR
eg:
TO_CHAR(E1,'E2 |FORMAT')

- E1=> DATE OR NUMBER AS INPUT

- 'E2'=> FORMAT IN WHICH WE REQUIRE THE O/P

- SELECT  TO_CHAR(SYSDATE,'DD-MONTH-YYYY')

FROM DUAL

- SELECT  TO_CHAR(SYSDATE,'Day "the " DdSPTH-Month-Year')

FROM DUAL

Point to note:
- FM SPECIFIER REMOVES THE ZERO OR BLANK SPACES

- SELECT  TO_CHAR(SYSDATE,'FMDD-MM-YYYY')

FROM DUAL

- SELECT  TO_CHAR(SYSDATE,'D,DAY ,DD-MONTH-YYYY')

FROM DUAL

- SELECT  LAST_NAME,TO_CHAR(SALARY,'$999,999.00')  SAL
FROM EMPLOYEES

WHERE DEPARTMENT_ID=30

- SELECT  LAST_NAME,TO_CHAR(SALARY,'L999,999.00',

 'NLS_CURRENCY=RUPEES') SAL
FROM EMPLOYEES

WHERE DEPARTMENT_ID=30

TO_DATE:

- TO_DATE(E1,E2)

- E1=> DATA AS CHARACTER REPRESENTATION

E2 => FORMAT SAME AS I/P

eg:

- SELECT SYSDATE-TO_DATE('JANUARY-01-2010','MONTH-DD-YYYY')

FROM DUAL

- SELECT SYSDATE-TO_DATE('01-01-2010','MM-DD-YYYY')

FROM DUAL

-Point to Note:

FX => CHECKS FOR THE EXACT MATCH IN SYNTAX BETWEEN

          I/P AND FORMAT

- SELECT SYSDATE-TO_DATE('FX01-01-2010','MM-DD-YYYY')

FROM DUAL

TO_NUMBER:

- TO_NUMBER(E1,E2)

- E1 => NUMBER AS CHARACTER

E2 => FORMAT SAME AS I/P

- SELECT TO_NUMBER('$657,786','$999,999')

FROM DUAL