toggle menu

Oracle 형변환 함수 정리

2012.07.10 10:36 Database/Oracle



TO_CHAR, TO_DATE, TO_NUMBER 

SQL> SELECT SYSDATE 날짜, TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL;

날짜     TO_CHAR(SY                           
-------- ----------                           
12/07/09 2012-07-09                           


SQL> SELECT TO_CHAR(12345, 'L999,999,999') "숫자를 문자 형태로 표현" FROM DUAL;

숫자를 문자 형태로 표                         
----------------------                        
              ₩12,345                        


SQL> SELECT ENAME, HIREDATE FROM EMP WHERE HIREDATE=TO_DATE(19810220,'YYYYMMDD');

ENAME      HIREDATE                           
---------- --------                           
ALLEN      81/02/20                           



SQL> SELECT ENAME, TO_DATE(20001231, 'YYYY/MM/DD')- HIREDATE FROM EMP WHERE ENAME='SCOTT';

ENAME      TO_DATE(20001231,'YYYY/MM/DD')-HIREDATE              
---------- ---------------------------------------              
SCOTT                                         5005              

SQL> SELECT ENAME 이름 , HIREDATE 입사일, TO_DATE(20001231, 'YYYY/MM/DD') 퇴사일, MONTHS_BETWEEN(TO_DATE(20001231, 'YYYY/MM/DD'), HIREDATE) 근무개월 FROM EMP WHERE ENAME='SCOTT';

이름       입사일   퇴사일     근무개월       
---------- -------- -------- ----------       
SCOTT      87/04/19 00/12/31 164.387097       



SQL> SELECT TO_NUMBER('20,000', '99,999') - TO_NUMBER('10,000', '999,999') FROM DUAL;

TO_NUMBER('20,000','99,999')-TO_NUMBER('10,000','999,999')      
----------------------------------------------------------      
                                                     10000      



SQL> SELECT TO_NUMBER('20,000', '999999') - TO_NUMBER('10,000', '999,999') FROM DUAL;

TO_NUMBER('20,000','999999')-TO_NUMBER('10,000','999,999')      
----------------------------------------------------------      
                                                     10000      


Database/Oracle 관련 포스팅 더보기