Oracle Calendar table

오라클 일자테이블 생성


SELECT TO_CHAR (MYDATE, 'yyyymmdd')   AS DATE_CD,
       TO_CHAR (MYDATE, 'yyyy')                 AS YEAR_CD,
       TO_CHAR (MYDATE, 'yyyy')||'년'          AS YEAR_NM,
       TO_CHAR (MYDATE, 'MM')                   AS MONTH_CD,
       TO_CHAR (MYDATE, 'MM')||'월'            AS MONTH_NM,
       TO_CHAR (MYDATE, 'DD')                   AS DAY_CD,
       TO_CHAR (MYDATE, 'DD')||'일'            AS DAY_NM,
       TO_CHAR (MYDATE, 'D')                     AS WEEK_CD,
       TO_CHAR (MYDATE, 'Day')                  AS WEEK_NM,
       TO_CHAR (MYDATE, 'WW')                  AS YEAR_WEEK_CD,
       TO_CHAR (MYDATE, 'WW')||'주차'        AS YEAR_WEEK_NM,
       TO_CHAR (MYDATE, 'Q')                     AS QRTR_CD,
       TO_CHAR (MYDATE, 'Q')||'/4분기'        AS QRTR_NM,
       DECODE( TO_CHAR (MYDATE, 'Q'), '1','1','2','1','3','2','4','2')                      AS HALF_CD,
       DECODE( TO_CHAR (MYDATE, 'Q'), '1','상반기','2','상반기','3','하반기','4','하반기')  AS HALF_NM
  FROM (
        SELECT TRUNC (ADD_MONTHS (SYSDATE, -60), 'yy') - 1 + LEVEL AS MYDATE
           FROM DUAL
         CONNECT BY LEVEL <= (SELECT   TRUNC (ADD_MONTHS (SYSDATE, 60), 'yy')
                                     - TRUNC (ADD_MONTHS (SYSDATE, -12), 'yy')
                                FROM DUAL
                             )
       );