在表day_master_temp中插入未来一年的日期数据，留有盈余两个月(14个月)
insert into day_master_temp(date1)(select date_dt from (select add_months(trunc(current_timestamp),14)-level as date_dt from dual connect by level < add_months(trunc(current_timestamp),14)-trunc(current_timestamp) order by date_dt )) --current_timestamp取当前系统时间戳 --trunc(current_timestamp)取到当天零时 --connect by 递归调用 --level递归的层 --add_months日期函数,加n个月后是哪天
在表day_master_temp中更新未来一年日期数据对应的年、月、日、季度、星期、周（以不同的星期算一周）
---year1 update day_master_temp t set t.year1=to_char(date1,'yyyy') ---year_month update day_master_temp t set t.year_month=to_char(date1,'yyyy-mm') ---month_num update day_master_temp t set t.month_num=to_number(to_char(date1,'mm')) ---day_num update day_master_temp t set t.day_num=to_number(to_char(date1,'dd')) ---xingqi update day_master_temp t set t.xingqi=to_char(date1,'day') ---flag 星期四 update day_master_temp t set t.flag='week_last_day' where t.xingqi='星期四' ---week_num1 week_num iw表示一年中的第几周 update day_master_temp t set t.week_num1=to_char(t.date1+2,'iw') update day_master_temp t set t.week_num=to_char(t.date1+2,'iw') ---week_num1 year_week update day_master_temp t set t.year_week=to_char(t.date1,'yyyy') || '-' ||to_char(t.date1+2,'iw') ---week_num1 year_week 需处理年头年尾 update day_master_temp t set t.year_week=to_char(t.date1,'yyyy') || '-' ||to_char(t.date1+2,'iw') ---week_original 周一开始 update day_master_temp t set t.week_original=to_char(t.date1,'iw') ---week_report 周五开始 update day_master_temp t set t.week_report=to_char(t.date1+3,'iw') ---week2 周六开始 update day_master_temp t set t.week2=to_char(t.date1+2,'iw') ---quarter 季度 update day_master_temp t set t.quarter=to_char(t.date1,'q')
Oracle数据库中周iw、ww、w的区别
ww：Same day of the week as the first day of the year——按年度1月1日的第一天为每周第一天
iw：Same day of the week as the first day of the ISO week, which is Monday—— 每周一
w：Same day of the week as the first day of the month——按月份1日的第一天作为每周第一天
select --oracle求当年的第几周,从当年的第一个星期一算当年第一周的第一天 to_char(TO_DATE('20110101', 'YYYYMMDD'), 'yyyyiw') as week, --oracle求当年的第几周,从当年的1月1日算一年的第一周的第一天 to_char(TO_DATE('20110101', 'YYYYMMDD'), 'yyyyww') as week2, --oracle求当月的第几周,从当月的1号算一周的第一天 to_char(TO_DATE('20110201', 'YYYYMMDD'), 'yyyyw') as week3, --oracle求第几年 to_char(TO_DATE('20110101', 'YYYYMMDD'), 'yyyy') as year, --oracle求当年的第几月 to_char(TO_DATE('20110101', 'YYYYMMDD'), 'yyyymm') as month, --oracle求当年的第几天 to_char(TO_DATE('20110101', 'YYYYMMDD'), 'yyyyddd') as day, --oracle求当年的第几季度 to_char(TO_DATE('20110401', 'YYYYMMDD'), 'yyyyq') as quarter from dual
WEEK WEEK2 WEEK3 YEAR MONTH DAY QUARTER
201152 201101 20111 2011 201101 2011001 20112