농부지기

3-14) TO_CHAR

1. 정의 : 숫자나 날짜를 문자열로 변환

2. 형식 : TO_CHAR(d, n) 
          - d : 숫자나 날짜타입 데이터
          - n : 변환_형식 

3. 사용예) SELECT  TO_CHAR(3980000, '$9,999,999') FROM DUAL;
     결과)  $3,980,000   (단, 숫자 이므로 $앞에 공백한칸이 붙게 된다)
   사용예) SELECT  TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL;
     결과) 2008-03-17 

4. 숫자용 포맷
  --------------------------------
   형식          설명
  --------------------------------
    9            숫자의 출력
    0            숫자열 앞에 0 표시
    .(소수점)    소수점 자리 표시
    ,(콤마)      지정한 위치에 콤마 표시
    $            달러 표시
  --------------------------------
    L            국가별 화폐단위 표시
    MI           오른쪽에 마이너스 부호
    EEEE         과학적 표기
  --------------------------------

5. 날짜를 문자변경 시 포맷
  ------------------------------------------------------------------------------
      형식                    설명
  ------------------------------------------------------------------------------
     YYYY  또는 SYYY          연도,S를 지정하면 기원전 연도에 -가 붙음.
     YYY  YY  또는 Y          년의 아래 3자리, 2자리, 1자리
     SYEAR 또는 YEAR          년을 철자로
     Q                        분기
     MM                       월
     MONTH 또는 MON           월의 명칭 또는 3문자의 단축형
     DDD  DD  또는 D          년,월,주의 몇 번째 날인가를 보여줌
     DAY  또는 DY             요일 또는 3문자의 단축형
     AM 또는 PM               오전과 오후 표시
     A.M. 또는 P.M.           오전과 오후 표시
     HH  또는 HH12            시간 (1-12)
     HH24                     시간(0-23)
     MI                       분
     SS                       초
  ------------------------------------------------------------------------------
     TH                       서수(DDPH라고 지정하면 31TH와 같이 나옴)
     SP                       숫자를 철자로 보여준다. (DDSP -> FOUR)
     SPTH  또는 THSP          서수를 철자로 보여준다. (DDSPTH => FOURTH)
  ------------------------------------------------------------------------------

☞ TO_NUMBER   

1. 정의 : 문자타입을 숫자타입으로 변환  
2. 사용예) SELECT  TO_NUMBER('10')) + 20 FROM DUAL;
     결과) 30

☞ TO_DATE  

1. 정의 : 문자타입을 날짜타입으로 변환
2. 형식 : TO_DATE(날짜형_문자값, 변환_형식) 
2. 사용예) 
     결과)  

☞  ADD_MONTHS 

1. 정의 : 지정한 날짜로부터 특정 개월을 더한 날짜를 구함 
2. 사용예) SELECT  ADD_MONTHS(SYSDATE, 25) FROM DUAL;
     결과) 2010/04/17 17:04:36   
3. 참고 : -(minus)값일 경우  월을 차감한다.

1. 정의 : 현재 시스템의 날짜 및 시간을 구함
2. 사용예) SELECT  SYSDATE FROM DUAL;
     결과) 2008/03/17 16:52:16   

☞ LAST_DAY

1. 정의 : 지정한 날짜의 해당 월의 말일을 구함
2. 사용예) SELECT  LAST_DAY(SYSDATE) FROM DUAL;
     결과) 2008/03/31 16:52:16

1. 정의 : 지정한 날짜로부터 가장 가깝게 다가올 해당 요일의 날짜를 구함
2. 기능 : 요일구분 : 1 - 일요일
                     2 - 월요일 ....
                     7 - 토요일
3. 사용예) SELECT  NEXT_DAY(TO_DATE('20080317'), 1) FROM DUAL;
     결과) 2008/03/22 00:00:00 

1. 정의 : 두 날짜 상이의 개월 수를 구함
2. 사용예) SELECT  MONTHS_BETWEEN(TO_DATE('20100505'), SYSDATE) FROM DUAL;
     결과) 25.5900....   

1. 정의 : 날짜에 대한 요일 구하기 
2. 사용예) SELECT  TO_CHAR(SYSDATE, 'DY') FROM DUAL;
     결과) 환경에 따라서 : 월,화,수,~~    일  (한글)
                           MON,THE,WED~~, SUN  (영문)
     * 항상 한글로 원할 경우
           (Oracle속성을 바꿀 수도 있지만 아래와 같이 한 sql에서만 적용시킬수도 있다)
           SELECT  TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE=KOREAN') FROM DUAL;

   사용예2) SELECT  TO_CHAR(SYSDATE, 'D') FROM DUAL;
       결과) 1,2 ~~ 7;  --숫자로 나옴 (1:일, 2:월, ~~  7:토요일)

☞ 월별 주차 구하기

1. 정의 : 해당 월별로 주차(1~6주)를 구한다.
2. 1주를 -> 일 ~ 토 까지 기준일 경우 SQL
   SELECT '20101205'
     , CEIL((substrb('20101205', -2, 2) + 7 - TO_CHAR(TO_DATE('20101205', 'yyyymmdd'), 'D')) / 7) as "주차"
  FROM DUAL;

3. 1주를 -> 월 ~ 일 까지 기준일 경우 SQL - 종류1 (만약, 매월1일이  일요일이면   02일을 1주로 본다.)
   SELECT  DECODE(WC, 0, 1, WC)
   FROM  (
           SELECT '20081231' DT
                , CEIL((substrb('20081231', -2, 2) + 7 - DECODE(TO_CHAR(TO_DATE('20081231', 'yyyymmdd'), 'D'), 1, 1, 0)
                        - DECODE(TO_CHAR(TO_DATE('20081231','yyyymmdd'),'D'), 1, 7, (TO_CHAR(TO_DATE('20081231','yyyymmdd'),'D')))
                       ) / 7) as WC
             FROM DUAL
         )
    ;

3. 1주를 -> 월 ~ 일 까지 기준일 경우 SQL - 종류2 (만약, 매월1일이  일요일이면   02일을 1주로 본다.)
                                         - 위 종류1 SQL문장을 좀더 보기 좋게  종류2 SQL 문으로 단순 변화만 시켰음.
  SELECT CEIL((DD + 7 - SUN- DECODE( YO, 1, 7, YO)) / 7) as WC
  FROM (
        SELECT SUBSTR('20090302', -2, 2)                                                                                   AS DD
             , DECODE(SUBSTR('20090302', -2, 2), '01', 0, DECODE(TO_CHAR(TO_DATE('20090302', 'yyyymmdd'), 'D'), 1, 1, 0))  AS SUN
             , TO_CHAR(TO_DATE('20090302', 'yyyymmdd'), 'D')                                                               AS YO
        FROM   DUAL
       ) ;

3. 1주를 -> 월 ~ 일 까지 기준일 경우 SQL - 종류3  (만약, 매월1일이  일요일이면   02일을 2주로 본다.)
  SELECT CEIL((DD + 7 + SUN- DECODE( YO, 1, 7, YO)) / 7) as WC
  FROM (
        SELECT SUBSTR('20090301', -2, 2)                                                                                    AS DD
             , DECODE(SUBSTR('20090301', -2, 2), '01', 0, DECODE(TO_CHAR(TO_DATE('20090301', 'yyyymmdd'), 'D'), 1, -1, 1))  AS SUN
             , TO_CHAR(TO_DATE('20090301', 'yyyymmdd'), 'D')                                                                AS YO
        FROM   DUAL
       ) ;

A. 정의 : 월별  몇주차까지 존재 하는지 조회
   예문1 : 201012   월에 대해서   몇주차까지 있는지 조회

  SELECT CEIL((DD + 7 + SUN- DECODE( YO, 1, 7, YO)) / 7) as WC
  FROM (
        SELECT SUBSTR(WORKDT, -2, 2)                                                                                AS DD
             , DECODE(SUBSTR(WORKDT, -2, 2), '01', 0, DECODE(TO_CHAR(TO_DATE(WORKDT, 'yyyymmdd'), 'D'), 1, -1, 1))  AS SUN
             , TO_CHAR(TO_DATE(WORKDT, 'yyyymmdd'), 'D')                                                            AS YO
        FROM   (SELECT TO_CHAR(ADD_MONTHS('201012' || '01', 1) - 1, 'YYYYMMDD') WORKDT FROM DUAL)
       ) ;
  결과 : 5

   예문2 : 2010년도 1~12월까지  각월별 주차 구하기

  SELECT CEIL((DD + 7 + SUN- DECODE( YO, 1, 7, YO)) / 7) as WC
  FROM (
        SELECT SUBSTR(WORKDT, -2, 2)                                                                                AS DD
             , DECODE(SUBSTR(WORKDT, -2, 2), '01', 0, DECODE(TO_CHAR(TO_DATE(WORKDT, 'yyyymmdd'), 'D'), 1, -1, 1))  AS SUN
             , TO_CHAR(TO_DATE(WORKDT, 'yyyymmdd'), 'D')                                                            AS YO
        FROM   (SELECT TO_CHAR(ADD_MONTHS('201012' || '01', 1) - 1, 'YYYYMMDD') WORKDT
                FROM DUAL, (SELECT to_char(rownum, '00') rn FROM dual connect by level <= 12)
                )
       ) ;

☞ 년간 주차 구하기

1. 정의 : 년간 주차 (1~52주)를 보여준다.

2. 한 날짜에 대한 년간 주차 구하기
  SELECT '20090115' as "날짜"
       , CEIL(  (TO_CHAR(TO_DATE('20090115','yyyymmdd'),'ddd')
              + TO_CHAR(TO_DATE(SUBSTR('20090115',1,4)||'0101','yyyymmdd'),'d') - 1) / 7)   DDATE
   FROM dual

3. 2010-12-10 부터 2011-01-15 까지 두 날짜 사이의 년간주차를 구하기

   SELECT TO_CHAR(TRUNC(TO_DATE('2010-12-10','YYYY-MM-DD') + ((LEVEL-1)*7), 'IW') + 5, 'YYYY')  YYYY
        , TO_CHAR(TRUNC(TO_DATE('2010-12-10','YYYY-MM-DD') + ((LEVEL-1)*7), 'IW'), 'IW') WW
        , TRUNC(TO_DATE('2010-12-10','YYYY-MM-DD') + ((LEVEL-1)*7), 'IW') - 1  BEGIN_DAY
        , TRUNC(TO_DATE('2010-12-10','YYYY-MM-DD') + ((LEVEL-1)*7), 'IW') + 5  END_DAY
    FROM DUAL
   CONNECT BY LEVEL <= (  (TRUNC(TO_DATE('2011-01-15','YYYY-MM-DD'), 'IW')+5) -- 검색종료일
                        - (TRUNC(TO_DATE('2010-12-10','YYYY-MM-DD'), 'IW')-1) -- 검색시작일
                        + 1
                       ) / 7 + 1 ;

☞ 시간 더하기

WITH T(DT) AS
(
               SELECT SYSDATE - (1/24) * 2    FROM DUAL  --현재시각에서 2시간 전
     UNION ALL SELECT SYSDATE - (1/24) * 1    FROM DUAL  --현재시각에서 1시간 전
     UNION ALL SELECT SYSDATE + (1/24) * 0.5  FROM DUAL  --현재시각에서 30분 후
     UNION ALL SELECT SYSDATE + (1/24) * 1    FROM DUAL  --현재시각에서 1시간
     UNION ALL SELECT SYSDATE + (1/24) * 2    FROM DUAL  --현재시각에서 2시간 후
     UNION ALL SELECT SYSDATE + (1/24) * 2.25 FROM DUAL  --현재시각에서 2시간 20분 후
)
SELECT DT
  FROM T ;
 

SELECT TO_CHAR(SYSDATE, 'YYYYMMDD HH24MISS'               ) C_TIME,   -- 현재시간
       TO_CHAR(SYSDATE + 1/24,'YYYYMMDD HH24MISS'         ) TIME,     -- 1시간 후
       TO_CHAR(SYSDATE + 40/(24*60),'YYYYMMDD HH24MISS'   ) MIN,      -- 40분 후
       TO_CHAR(SYSDATE + 10/(24*60*60),'YYYYMMDD HH24MISS') SEC       -- 10초 후
  FROM DUAL;

1. 정의 : 날짜에 대한 반올림처리

1. 정의 : 날짜에 대한 버림처리 

☞ ROUND

1. 정의 : 기준 숫자값에 일정자릿수에서 반올림처리
2. 사용예) SELECT  ROUND(3009.345, 2) FROM DUAL;
     결과) 3009.35   

☞ TRUNC 

1. 정의 : 기준 숫자값에 일정자릿수에서 절사처리
2. 형식 : TRUNC(n, [m])   또는   TRUNC(d, f)
          - n : 반내림을 수행할 임의의 숫자
          - m : 반내림을 수행할 때 기준이 되는 자리 수 (양또는 음의 정수)
          - d : 날짜 타입의 데이터
          - f : 재공되는 날짜 포맷 형식
   사용예) SELECT  TRUNC(3009.345, 2) FROM DUAL;
     결과) 3009.34

☞ MOD

1. 정의 : 두 숫자를 나눈 후 나머지를 구함
2. 사용예) SELECT  MOD(7, 2) FROM DUAL;
     결과) 1 

☞ POWER

1. 정의 : 기준 숫자값에서 거듭제곱 값을 구한다.
2. 사용예) SELECT  POWER(3, 2) FROM DUAL;
     결과) 9   

☞ SQRT  

1. 정의 : 기준 숫자값에서 제곱근 값을 구한다.
2. 사용예) SELECT  SQRT(25) FROM DUAL;
     결과)  5    

☞ SIGN  

1. 정의 : 기순 숫자값이 양수인지? 은수인지? 0 인지를 구한다.
2. 기능 : 양수이면 -> +1
          0   이면 ->  0
          음수이면 -> -1
3. 사용예) SELECT  SIGN(-3009) FROM DUAL;
     결과) -1 

☞ CHR

1. 정의 : 숫자에 다른 ASCII값에 해당하는 문자를 구함
2. 사용예) SELECT  CHAR(65),  CHAR(66) FROM DUAL;
     결과) A,  B  

☞  숫자 FORMAT지정

1. 정의 : 숫자 결과에 format지정하기
2. 사용예)
   SELECT TO_CHAR(10  , '000.00')                --> 결과 : 010.00
        , TO_CHAR(10  , '990.00')                --> 결과 : b10.00
        , TO_CHAR(100 , '990.00')                --> 결과 : 100.00
        , TO_CHAR(1.18, '990.00')                --> 결과 : bb1.18
        , TO_CHAR(222222.22, '999,999,990.00')   --> 결과 : 222,222.22  
     FROM DUAL;
3. 설명 : TO_CHAR( 숫자, 'format')
   format : 0  은 --> 값이 해당 자리수에 존재하면    해당값이 출력.
                      값이 해당 자리수에 미 존재하면 0 으로 출력
            9  는 --> 값이 해당 자리수에 존재하면    해당값이 출력.
                      값이 해당 자리수에 미 존재하면 b(공백) 으로 출력.
4. 추가설명 : 만약 format의 자릿수보다 클 경우 --> #####~~ 으로 출력됨 (오류 발생).