insert into t_date_dm
select
to_number(to_char(time,'yyyymmdd')) date_uid,
time date_id,
substr(trim(to_char(time,'DAY')),0,3) day_name,
decode(to_char(time, 'd')-1,0,7,
to_char(time, 'd')-1) cal_day_number_in_week,
trunc(time)-trunc(time,'month')+1 cal_day_number_in_month,
trunc(time)-TRUNC(time,'Q')+1 cal_day_number_in_quarter,
to_char(time,'ddd') cal_day_number_in_year,
to_char(time,'ww') cal_week_number,
case when trunc(time)-add_months(trunc(last_day(time)),-1)<=7 then 1
when trunc(time)-add_months(trunc(last_day(time)),-1)>=8 and trunc(time)-add_months(trunc(last_day(time)),-1)<=14 then 2
when trunc(time)-add_months(trunc(last_day(time)),-1)>=15 and trunc(time)-add_months(trunc(last_day(time)),-1)<=21 then 3
when trunc(time)-add_months(trunc(last_day(time)),-1)>=22 and trunc(time)-add_months(trunc(last_day(time)),-1)<=28 then 4
else 5 end cal_week_number_in_month,
case when trunc(time)-add_months(trunc(last_day(time)),-1)<=7 then add_months(trunc(last_day(trunc(time))+1),-1)+6
when trunc(time)-add_months(trunc(last_day(time)),-1)>=8 and trunc(time)-add_months(trunc(last_day(time)),-1)<=14 then add_months(trunc(last_day(trunc(time))+1),-1)+13
when trunc(time)-add_months(trunc(last_day(time)),-1)>=15 and trunc(time)-add_months(trunc(last_day(time)),-1)<=21 then add_months(trunc(last_day(trunc(time))+1),-1)+20
when trunc(time)-add_months(trunc(last_day(time)),-1)>=22 and trunc(time)-add_months(trunc(last_day(time)),-1)<=28 then add_months(trunc(last_day(trunc(time))+1),-1)+27
else trunc(last_day(trunc(time))+1) end cal_week_ending_day,
to_number(to_char(time,'yyyymm')) cal_month_no,
substr(trim(to_char(time,'MONTH')),0,3) cal_month_name,
to_char(time,'yyyy-mm') cal_month_desc,
trunc(to_char(time,'mm')) cal_month_number,
trunc(last_day(time))-add_months(trunc(last_day(time))+1,-1) cal_days_in_month,
add_months(trunc(last_day(time))+1,-1) cal_begin_of_month,
trunc(last_day(time)) cal_end_of_month,
to_number(to_char(time,'yyyy')||''||decode(to_number(to_char(TRUNC(time,'Q'),'mm')),1,1,4,2,7,3,10,4)) cal_quarter_no,
to_char(time,'yyyy')||'-Q'||decode(to_number(to_char(TRUNC(time,'Q'),'mm')),1,1,4,2,7,3,10,4) cal_quarter_desc,
decode(to_number(to_char(TRUNC(time,'Q'),'mm')),1,1,4,2,7,3,10,4) cal_quarter_number,
last_day(last_day(last_day(TRUNC(time,'Q'))+1)+1)-TRUNC(time,'Q') cal_days_in_quarter,
TRUNC(time,'Q') cal_begin_of_quarter,
last_day(last_day(last_day(TRUNC(time,'Q'))+1)+1) cal_end_of_quarter,
to_number(to_char(time,'yyyy')) cal_year_no,
add_months(trunc(time,'YYYY'),12)-1-trunc(time,'YYYY') cal_days_in_year,
trunc(time,'YYYY') cal_begin_of_year,
add_months(trunc(time,'YYYY'),12)-1 cal_end_of_year
from (select to_date(20170101,'yyyymmdd')+rownum-1 time from dual connect by rownum<36540)
