oracle 数据库中生成日历的方法

直接上代码：

  
SELECT to_date('20180101','yyyymmdd') + level-1 as everyDay from dual 
connect by level <= 
(last_day(to_date('20181201','yyyymmdd')) - to_date('20180101','yyyymmdd') +1);---这是日历

 

下面是对日历进行详细的操作

select to_char(everyDay,'yyyy-mm-dd') as dt,
   to_char(everyday,'yyyy') as yr,
   to_char(everyday,'mm') as mm,
   to_char(everyday,'dd') as dd,
   to_char(everyday,'dy') as dayofweek,
   /*ORACLE自定义的标准周*/
   to_char(everyday,'WW') as weeknum,
   /*该月的第几周*/
   /*lpad(to_char(everyday,'w'),6) as monthOfWeek,*/
   to_char(everyday,'Q') as qr,
   /*ISO的标准周，通常使用这个*/
   to_char(everyday,'IW') as bourse_week,
   null as id
   from(select to_date('20180101','yyyymmdd') + level - 1 as everyDay from dual
  connect by level <= 
  (last_day(to_date('20181201','yyyymmdd')) - to_date('20180101','yyyymmdd') +1));

 

注意：这个垃圾的文本编辑器，会出现字符修改，粘贴后需要进行编辑，才能进行正常使用

 

 

-- 生成日历

select to_char(veryday,'yyyy') year,
          to_char(veryday,'mm') month,
          to_char(veryday,'dd') day,
          to_char(veryday-1,'d') week
          
          from 
(select (to_date('20170101','yyyymmdd') + level -1) veryday   from dual 
connect by level <= 
(select last_day(to_date('20171231','yyyymmdd')) - to_date('20170101','yyyymmdd')+1 from dual ))