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('', 'yyyymmdd') + level - 1 as everyDay
from dual
connect by level <= (last_day(to_date('', 'yyyymmdd')) -
to_date('', 'yyyymmdd') + 1));
结果图:
替换sql语句中的年份即可生成当年的数据。