Oracle之一份标准日历表的构建

select TO_NUMBER(to_char(everyDay,'yyyymmdd')) as ID,

       everyDay as CODE,

       to_char(everyday,'yyyy')||'年'||TO_NUMBER(to_char(everyday,'mm'))||'月'||TO_NUMBER(to_char(everyday,'dd'))||'日' AS NAME,

   TO_NUMBER(to_char(everyday,'yyyy')) as YEAR_ID,

   to_char(everyday,'yyyy')||'年' as YEAR_NAME,

   CASE WHEN TO_NUMBER(to_char(everyday,'mm'))<=6 THEN 1 ELSE 2 END AS SEMESTER_ID,

   CASE WHEN TO_NUMBER(to_char(everyday,'mm'))<=6 THEN '上半年' ELSE '下半年' END AS SEMESTER_NAME,

   CASE WHEN TO_NUMBER(to_char(everyday,'mm'))<=3 THEN 1

     WHEN TO_NUMBER(to_char(everyday,'mm'))<=6 THEN 2

       WHEN TO_NUMBER(to_char(everyday,'mm'))<=9 THEN 3

         ELSE 4 END AS QUARTER_ID,

   CASE WHEN TO_NUMBER(to_char(everyday,'mm'))<=3 THEN '一季度'

     WHEN TO_NUMBER(to_char(everyday,'mm'))<=6 THEN '二季度'

       WHEN TO_NUMBER(to_char(everyday,'mm'))<=9 THEN '三季度'

         ELSE '四季度' END AS QUARTER_NAME,        

   TO_NUMBER(to_char(everyday,'mm')) as MONTH_ID,

   TO_NUMBER(to_char(everyday,'mm'))||'月' as MONTH_NAME,

   CASE WHEN TO_NUMBER(to_char(everyday,'dd'))<=10 THEN '上旬' WHEN TO_NUMBER(to_char(everyday,'dd'))<=20 THEN '中旬' ELSE '下旬' END  as XUN,

   TO_NUMBER(to_char(everyday,'dd')) as DAY,

   CASE WHEN to_char(everyday,'dy')='星期一' THEN 1

        WHEN  to_char(everyday,'dy')='星期二' THEN 2 

        WHEN  to_char(everyday,'dy')='星期三' THEN 3

        WHEN to_char(everyday,'dy')='星期四' THEN 4

        WHEN to_char(everyday,'dy')='星期五' THEN 5

        WHEN to_char(everyday,'dy')='星期六' THEN 6

        ELSE 7  END   as WEEK_ID,

   to_char(everyday,'dy') as WEEK_NAME,

 --  lpad(to_char(everyday,'w'),6) as w,

 --  to_char(everyday,'WW') AS WW,

 --  to_char(everyday,'IW') AS IW,

--   to_char(everyday,'FMWW') AS FMWW,

   TO_NUMBER(lpad(to_char(everyday,'IW'),6)) as ISOWEEK_NUMBER_OF_YEAR

   from(select to_date('20100101','yyyymmdd') + level - 1 as everyDay from dual

  connect by level <= 

  (last_day(to_date('20111101','yyyymmdd')) - to_date('20100101','yyyymmdd') +1));

转载于:https://www.cnblogs.com/qjxiaopang/p/5015871.html