Oracle 生成工作日历表

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('20190101', 'yyyymmdd') + level - 1 as everyDay
          from dual
        connect by level <= (last_day(to_date('20191201', 'yyyymmdd')) -
                   to_date('20190101', 'yyyymmdd') + 1));

结果图：

 替换sql语句中的年份即可生成当年的数据。

转载于:https://www.cnblogs.com/Jackie-sky/p/10219907.html