Conversion Functions in Oracle

---------- Decimal Indicator ----------
SELECT 1234,
  TO_CHAR(1234,'9999D99')
FROM DUAL;
SELECT 1234, TO_CHAR(1234,'999D99') FROM DUAL;
---------- Specific Notation Indicator ----------
SELECT 1234,
  TO_CHAR(1234,'9.9EEEE'),
  TO_CHAR(1234,'9.99EEEE')
FROM DUAL;
---------- Group Seperator --------------------
SELECT 1234567,
  TO_CHAR(1234567,'99G99G999')
FROM DUAL;
SELECT SAL,TO_CHAR(SAL,'99G999D99') FROM ISC_EMP;
---------- Local Currency Indicator -----------
SELECT 1234,
  TO_CHAR(1234,'L9999')
FROM ISC_EMP;
SELECT SAL, TO_CHAR(SAL,'L999999') CURRENCY FROM ISC_EMP;
SELECT SAL,
  TO_CHAR(SAL,'L99G999D99','NLS_CURRENCY = RS') Sal
FROM ISC_EMP
WHERE DEPTNO = 20;
---------- Trailing Minus Indicator ----------
SELECT    -10000,
  TO_CHAR(-10000,'L99G999D99MI')
FROM DUAL;
SELECT SAL,
  COMM,
  TO_CHAR(SAL       -COMM,'L999999MI'),
  LENGTH(TO_CHAR(SAL-COMM,'L999999MI'))
FROM ISC_EMP;
--------- Negitive Number Indicator ----------
SELECT TO_CHAR(-1000,'L99G999D99PR')
FROM DUAL;
SELECT SAL,COMM,TO_CHAR(SAL-COMM,'L99G999D99PR') FROM ISC_EMP;
-------- Roman Number Indicator -------------
SELECT 1000,
  TO_CHAR(11,'RN'),
  TO_CHAR(10,'rn')
FROM DUAL;
-----------Sign Indicator -------------------
SELECT 1000,
  TO_CHAR(1000,'S9999'),
  TO_CHAR(-1000,'S9999')
FROM DUAL;
SELECT 1000, TO_CHAR(1000,'9999S'), TO_CHAR(-1000,'9999S') FROM DUAL;
SELECT SAL, TO_CHAR(SAL,'S99999'), TO_CHAR(SAL,'999999S') FROM EMP;
SELECT SAL,
  COMM,
  TO_CHAR(COMM-SAL,'S99999'),
  TO_CHAR(COMM-SAL,'99999S')
FROM ISC_EMP;
-------- Hexa Decimal Indicator -------------
SELECT 1000,
  TO_CHAR(1000,'XXXX')
FROM DUAL;
SELECT ENAME,SAL,TO_CHAR(SAL,'XXXX') FROM ISC_EMP;
------- Group Sepearator --------------------
SELECT 10000,
  TO_CHAR(10000,'99,999.99')
FROM DUAL;
SELECT ENAME,SAL,TO_CHAR(SAL,'99,999.99') FROM ISC_EMP;
---------- Decimal Indicator ---------------
SELECT 10000,
  TO_CHAR(10000,'L99,999.99')
FROM DUAL;
SELECT ENAME,SAL,TO_CHAR(SAL,'L99,999.99') FROM ISC_EMP;
--------- Dollar Indicator ------------------
SELECT 10000,
  TO_CHAR(10000,'$99,999.99')
FROM DUAL;
SELECT ENAME,SAL,TO_CHAR(SAL,'$99,999.99') FROM ISC_EMP;
--------- Zero Indicator ------------------
SELECT 1000,
  TO_CHAR(1000,'0999999'),
  TO_CHAR(1000,'09999990')
FROM DUAL;
SELECT ENAME,SAL,TO_CHAR(SAL,'$099,999,99') FROM ISC_EMP;
--------- ISO Currency Indicator ----------
SELECT 1000,
  TO_CHAR(1000,'C9999.99')
FROM DUAL;
SELECT ENAME,SAL, TO_CHAR(SAL,'C9999.99') FROM ISC_EMP;
--------- AD/BC Indicator ------------------
SELECT SYSDATE,
  TO_CHAR(SYSDATE,'AD')
FROM DUAL;
SELECT TO_CHAR(SYSDATE,'B.C.'), TO_CHAR(SYSDATE,'A.D.') FROM DUAL;
SELECT ENAME, SAL, HIREDATE,TO_CHAR(HIREDATE,'A.D.') FROM ISC_EMP;
----------- MERIDIAN INDICATOR -------------
SELECT SYSDATE,
  TO_CHAR(SYSDATE,'A.M.'),
  TO_CHAR(SYSDATE,'PM')
FROM DUAL;
SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'AM') FROM ISC_EMP;
------------ CENTURY INDICATOR -------------
SELECT SYSDATE,
  TO_CHAR(SYSDATE,'CC-AD')
FROM DUAL;
SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'CC-AD') FROM ISC_EMP;
------------- Numeric Week Day Indicator ---
SELECT SYSDATE,
  TO_CHAR(SYSDATE,'D')
FROM DUAL;
SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'D') FROM ISC_EMP;
------------ WEEK DAY SPELLING INDICATOR ---------
SELECT SYSDATE,
  TO_CHAR(SYSDATE,'DAY')
FROM DUAL;
SELECT ENAME,
  SAL,
  HIREDATE,
  TO_CHAR(HIREDATE,'DAY')
FROM ISC_EMP
WHERE TO_CHAR(HIREDATE,'DAY') = 'WEDNESDAY';
------------ Month Day Indicator ---------------
SELECT SYSDATE,
  TO_CHAR(SYSDATE,'DD-DAY')
FROM DUAL;
SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'DD') FROM EMP;
SELECT ENAME,
  SAL,
  HIREDATE,
  TO_CHAR(HIREDATE,'DD-DAY')
FROM ISC_EMP
WHERE TO_CHAR(HIREDATE,'DD-DAY') = '17-WEDNESDAY';
------------ Year Day Indicator --------------
SELECT SYSDATE,
  TO_CHAR(SYSDATE,'DDD')
FROM DUAL;
SELECT ENAME,
  SAL,
  HIREDATE,
  TO_CHAR(HIREDATE,'DDD')
FROM ISC_EMP
WHERE TO_CHAR(HIREDATE,'DD-DAY') = '17-WEDNESDAY';
------------ Abbreviated week day ------------
SELECT SYSDATE,
  TO_CHAR(SYSDATE,'DY')
FROM DUAL;
SELECT SYSDATE, TO_CHAR(SYSDATE,'D-DY-DAY') FROM DUAL;
SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'D-DY-DAY') FROM ISC_EMP;
-------------4 DIGIT YEAR INDICATOR --------
SELECT SYSDATE,
  TO_CHAR(SYSDATE,'YYYY')
FROM DUAL;
SELECT SYSDATE, TO_CHAR(SYSDATE,'YY') FROM DUAL;
SELECT SYSDATE, TO_CHAR(SYSDATE,'YYY') FROM DUAL;
SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'YYYY') FROM ISC_EMP;
----------- WEEK OF THE MONTH INDICATOR ------------
SELECT SYSDATE,
  TO_CHAR(SYSDATE,'W')
FROM DUAL;
SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'W') FROM ISC_EMP;
----------- YEAR WEEK INDICATOR ---------
SELECT SYSDATE,
  TO_CHAR(SYSDATE,'WW')
FROM DUAL;
SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'WW') FROM ISC_EMP;
---------- Quarter of the year indicator ------
SELECT SYSDATE,
  TO_CHAR(SYSDATE,'Q')
FROM DUAL;
SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'Q') FROM ISC_EMP;
----------JULIAN DAY---------------
SELECT SYSDATE,
  TO_CHAR(SYSDATE,'J')
FROM DUAL;
SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'J') FROM ISC_EMP;
---------- Numeric Month Indicator -----
SELECT SYSDATE,
  TO_CHAR(SYSDATE,'MM')
FROM DUAL;
SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'MM') FROM ISC_EMP;
---------- ABBREIVATED Month Indicator -----
SELECT SYSDATE,
  TO_CHAR(SYSDATE,'MON')
FROM DUAL;
SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'MON') FROM ISC_EMP;
----------  Month Spelling Indicator -----
SELECT SYSDATE,
  TO_CHAR(SYSDATE,'MON')
FROM DUAL;
SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'MON') FROM ISC_EMP;
----------- Hours Minutes Seconds --------
SELECT SYSDATE,
  TO_CHAR(SYSDATE,'HH')
FROM DUAL;
SELECT SYSDATE, TO_CHAR(SYSDATE,'HH24') FROM DUAL;
SELECT SYSDATE, TO_CHAR(SYSDATE,'MI') FROM DUAL;
SELECT SYSDATE, TO_CHAR(SYSDATE,'SS') FROM DUAL;
SELECT SYSDATE, TO_CHAR(SYSDATE,'SSSSS') FROM DUAL;
SELECT SYSDATE, TO_CHAR(SYSDATE,'HH:MI:SS') FROM DUAL;
----------- Date format Element Suffixes -------TH OR SP
SELECT SYSDATE,
  TO_CHAR(SYSDATE,'DDSPTH MONTH YYYYSP')
FROM DUAL;
------------- Conversion Functions ------------- TO NUMBER ----
SELECT '$10,000.00',
  TO_NUMBER('$10,000.00','L99,999.99')
FROM DUAL;
SELECT '$10,000.00',TO_NUMBER('$10,000.00','L99,999.99')+500 FROM DUAL;
------------- Conversion Functions ------------- TO DATE ----
SELECT SYSDATE,
  ROUND('19-OCT-11','MONTH')
FROM DUAL;
SELECT SYSDATE, ROUND(TO_DATE('19-OCT-11'),'MONTH') FROM DUAL;
SELECT '12-AUGUST-2007','12-AUGUST-2007'+3 FROM DUAL;
SELECT '12-AUGUST-2007',TO_DATE('12-AUGUST-2007','DD-MONTH-YYYY')+3 FROM DUAL;