conversion

CONVERSIONS ARE OF TWO TYPES

1. IMPLICIT CONVERSION, 2. EXPLICIT CONVERSION

Implicit Conversion -> is automatically done by oracle server.

Explicit Conversion -> conversion done by user/programmer by using conversion

functions;

eg,

SELECT * FROM emp WHERE hiredate < '01-JAN-1982'; -- implict conversion

SELECT * FROM emp WHERE hiredate < 'JAN-01-1982'; -- cannot do implicit conversion

SELECT sal * '12' AS "Annual Salary" FROM emp; -- implicit conversion

SELECT sal * '$12' FROM emp; -- cannot do implict conversion

SELECT * FROM emp WHERE hiredate < 'JAN-01-1982';

SELECT * FROM emp WHERE hiredate < TO_DATE('JAN-01-1982', 'MON-DD-YYYY'); -- explicit conversion

SELECT * FROM EMP WHERE HIREDATE < 'JANUARY 26, 1982';

SELECT * FROM emp WHERE hiredate < to_date('JANUARY 26, 1982','MONTH DD, RRRR');

SELECT SAL * '$12' FROM EMP;

SELECT sal * TO_NUMBER('$12', '$99') AS annual_salary FROM emp;

SELECT * FROM EMP WHERE SAL > '2,000.00';

SELECT * FROM emp WHERE sal > TO_NUMBER('2,000.00', '9,999.00');

SELECT * FROM EMP WHERE SAL > TO_NUMBER('2,000.00', '9G999D00');

-- Dt.10-Mar-2014 --

SELECT HIREDATE FROM EMP;

requirement:

17th, DECEMBER Nineteen Eighty.

SELECT HIREDATE, TO_CHAR(HIREDATE,'DDTH, MONTH YEAR') DATE_TO_CHAR FROM EMP;

SELECT HIREDATE, TO_CHAR(HIREDATE,'FMDDTH, MONTH YEAR') DATE_TO_CHAR FROM EMP;

Note:fm stands for fill mode.

SELECT HIREDATE, TO_CHAR(HIREDATE,'FMDDth, Month Year') DATE_TO_CHAR FROM EMP;

SELECT hiredate, TO_CHAR(hiredate,'DDSP, MONTH YEAR') FROM emp;

SELECT hiredate, TO_CHAR(hiredate,'DDSPTH, Month Year') FROM emp;

SELECT hiredate, TO_CHAR(hiredate,'fmDDSPTH, Month Year') FROM emp;

SELECT hiredate, TO_CHAR(hiredate,'DD, Month Year') FROM emp;

SELECT hiredate, TO_CHAR(hiredate,'fmDD, Month Year') FROM emp;

SELECt hiredate, TO_CHAR(hiredate, 'Day, Month Year') FROM emp;

SELECt hiredate, TO_CHAR(hiredate, 'fmDay, Month Year') FROM emp;

SELECt hiredate, TO_CHAR(hiredate, 'Dy, Month Year') FROM emp;

SELECt hiredate, TO_CHAR(hiredate, 'DD, MM Year') FROM emp;

SELECT TO_CHAR(sysdate, 'J') FROM DUAL;

SELECT sal FROM emp;

Requirement:

1600  - $1,600.00

SELECT sal, TO_CHAR(sal, '$99,99,999.99') sal1 FROM emp;

SELECT sal, TO_CHAR(sal, '$999.99') sal1 FROM emp;

L - useful to display local currency.

SELECT sal, TO_CHAR(sal, 'L99,999.99') sal1 FROM emp;

ALTER SESSION SET nls_currency='Rs';

SELECT sal, TO_CHAR(sal, 'L99,999.99') sal1 FROM emp;

SELECT sal, TO_CHAR(sal, '$99,999.99') sal1 FROM emp;

SELECT sal FROM emp;

SELECT sal, TO_CHAR(TO_DATE(sal, 'J'), 'JSP') FROM emp;

SELECT ename, hiredate FROM emp

WHERE hiredate < TO_DATE('January 01, 81', 'Month DD,    YY');

SELECT ename, hiredate FROM emp

WHERE hiredate < TO_DATE('January 01,81','fxMonth DD,YY');

fx stands for fixed number of characters.

SELECT ename, to_char(hiredate, 'dd-mon-yyyy') FROM emp

WHERE hiredate < TO_DATE('January 01,81','Month DD, yy');

select to_char(to_date('january 01, 81','month dd, yy'),'month dd, yyyy') from dual;

SELECT ename, hiredate FROM emp

WHERE hiredate < TO_DATE('January 01,81','Month DD, rr');

select to_char(to_date('january 01, 81','month dd, rr'),'month dd, yyyy') from dual;

RR FORMAT TABLE

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

CURRENT_DATE -> 12-mar-2014

                                  0-49                            50-99

                                 

IF I GIVE DATE  20-JUN-30   -> 20-JUN-2030

IF I GIVE DATE  20-JUN-60   ->                                   20-JUN-1960

CURRENT_DATE ->                                             12-mar-2070

                                0-49                            50-99

IF I GIVE DATE  20-JUN-80   ->                                20-JUN-2080

IF I GIVE DATE  20-JUN-30   -> 20-JUN-2130

SELECT SYSDATE FROM DUAL;

SELECT TO_CHAR(sysdate,'FMyyyy-month-dd') FROM dual;

SELECT ename, hiredate,  TO_CHAR(hiredate,'ddth,Mon-yyyy')

FROM emp WHERE deptno=10;

SELECT TO_CHAR(sysdate,'w') FROM dual;

'W' - WEEK OF THE MONTH

SELECT hiredate, TO_CHAR(hiredate,'w') FROM emp;

'WW' - WEEK OF THE YEAR

select to_char(sysdate,'ww') from DUAL;

SELECT TO_CHAR(sysdate,'d') FROM dual;

SELECT TO_CHAR(sysdate,'dd') FROM dual;

SELECT TO_CHAR(sysdate,'ddd') FROM dual;

SELECT TO_CHAR(sysdate,'q') FROM dual;

SELECT SYSDATE FROM DUAL;

SELECT TO_CHAR(sysdate,'hh:mi:ss am') FROM dual;

SELECT TO_CHAR(sysdate,'hh24:mi:ss') FROM dual;

SELECT TO_CHAR(sysdate,'hh24:mi:ss.sssss') FROM dual;

SELECT '12-Mar-14' + 1 FROM DUAL;

SELECT TO_DATE('12-Mar-14', 'DD-Mon-YY')+1 FROM DUAL;

SELECT TO_DATE('12-Mar-14')+1 FROM DUAL;

SELECT TO_DATE('Mar-12-14')+1 FROM DUAL;

SELECT TO_DATE('Mar-12-14', 'Mon-DD-YY')+1 FROM DUAL;

SELECT TO_NUMBER('12') * 12 FROM DUAL;

SELECT TO_NUMBER('$12') * 12 FROM DUAL;

SELECT TO_NUMBER('$12', '$99') * 12 FROM DUAL;