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;