oracle生成日期维表,通用日期维表生成脚本

最新推荐文章于 2022-01-05 09:51:14 发布

吧啦吧啦变

最新推荐文章于 2022-01-05 09:51:14 发布

阅读量223

点赞数

文章标签： oracle生成日期维表

本文详细介绍了如何在SQL查询中使用复杂的日期函数，包括日期转换、星期几编号、季度和年份分析，以实现对时间数据的深入理解和操作。通过实例展示了如何计算一周、一月、一季和一年中的天数、开始和结束日期等信息。

摘要由CSDN通过智能技术生成

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,