oracle日期维度表

查询日期属性

select
	TO_NUMBER(TO_CHAR(sysdate,'yyyymmdd')) as ID,
	TRUNC(sysdate) as FDATE,--日期
	TO_CHAR(sysdate,'yyyy') || '-Q' || TO_CHAR(sysdate,'q') as YEAR_QUARTER,--年季度
	TO_CHAR(sysdate,'yyyy') || '-' || TO_CHAR(sysdate,'mm') as YEAR_MONTH,--年月
	TO_CHAR(sysdate,'yyyy') || '-' || TO_CHAR(sysdate,'ddd') as YEAR_DAY,--年天
	TO_CHAR(sysdate,'yyyy-mm-dd') as YEAR_MONTH_DAY,--年月日
	EXTRACT(year from sysdate) as YEAR,--年份
	TO_NUMBER(TO_CHAR(sysdate,'q')) as QUARTER_OF_YEAR,--当年季度
	EXTRACT(MONTH FROM SYSDATE) as MONTH_OF_YEAR,--当年月份
	months_between(trunc(sysdate, 'mm'),trunc(sysdate, 'q'))+1 as MONTH_OF_QUARTER,--当季度月份
	TO_NUMBER(TO_CHAR(sysdate, 'ww')) as WEEK_OF_YEAR,--当年周
	CEIL((TRUNC(sysdate)-TRUNC(sysdate, 'q')+1)/7) as WEEK_OF_QUARTER,--当季度周
	TO_NUMBER(TO_CHAR(sysdate, 'w')) as WEEK_OF_MONTH,--当月周
	TO_NUMBER(TO_CHAR(sysdate,'ddd')) as DAY_OF_YEAR,--当年天
	TRUNC(sysdate)-TRUNC(sysdate, 'q')+1 as DAY_OF_QUARTER,--当季度天
	EXTRACT(DAY FROM SYSDATE) as DAY_OF_MONTH,--当月天
	TRUNC(sysdate)-TRUNC(sysdate,'iw')+1 as DAY_OF_WEEK,--当周天
	TO_CHAR(sysdate, 'day') as WEEK,--星期
	case 
		when TRUNC(sysdate,'q')=trunc(sysdate, 'yyyy') then 1
		else 0
	end as IS_F_QUARTER_OF_YEAR,--是否当年第一季度
	case 
		when ADD_MONTHS(TRUNC (SYSDATE, 'YEAR'), 12) - 1 = trunc(add_months(sysdate,3), 'Q') -1 then 1
		else 0
	end as IS_L_QUARTER_OF_YEAR,--是否当年最后一季度
	case 
		when trunc(sysdate, 'mm')=trunc(sysdate, 'yyyy') then 1
		else 0
	end as IS_F_MONTH_OF_YEAR,--是否当年第一月
	case 
		when TRUNC (LAST_DAY (SYSDATE)) = ADD_MONTHS(TRUNC (SYSDATE, 'YEAR'), 12) - 1 then 1
		else 0
	end as IS_L_MONTH_OF_YEAR,--是否当年最后一月
	case 
		when TRUNC(sysdate,'q') = trunc(sysdate, 'mm') then 1
		else 0
	end as IS_F_MONTH_OF_QUARTER,--是否当季第一月
	case 
		when trunc(add_months(sysdate,3), 'Q') -1 = TRUNC (LAST_DAY (SYSDATE)) then 1
		else 0
	end as IS_L_MONTH_OF_QUARTER,--是否当季最后一月
	case 
		when trunc(sysdate)-trunc(sysdate, 'yyyy') < 7 then 1
		else 0
	end as IS_F_WEEK_OF_YEAR,--是否当年第一周	
	case 
		when TO_NUMBER(TO_CHAR(sysdate,'ddd'))-52*7 > 0 then 1
		else 0
	end as IS_L_WEEK_OF_YEAR,--是否当年最后一周
	case 
		when trunc(sysdate) - TRUNC(sysdate,'q') < 7 then 1
		else 0
	end as IS_F_WEEK_OF_QUARTER,--是否当季第一周
	case 
		when ceil((trunc(sysdate) - trunc(sysdate, 'q') +1)/7) = ceil(((trunc(add_months(sysdate,3), 'Q') -1) - trunc(sysdate, 'q') + 1)/7) then 1
		else 0
	end as IS_L_WEEK_OF_QUARTER,--是否当季最后一周
	case 
		when trunc(sysdate) - trunc(sysdate, 'mm') < 7 then 1
		else 0
	end as IS_F_WEEK_OF_MONTH,--是否当月第一周
	case 
		when ceil((TRUNC(sysdate) - trunc(sysdate, 'mm') + 1)/7) = ceil((TRUNC(LAST_DAY (SYSDATE)) - trunc(sysdate, 'mm') + 1)/7) then 1
		else 0
	end as IS_L_WEEK_OF_MONTH,--是否当月最后一周
	case 
		when TRUNC(sysdate) = trunc(sysdate, 'yyyy') then 1
		else 0
	end as IS_F_DAY_OF_YEAR,--是否当年第一天
	case 
		when TRUNC(sysdate) = (ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12) - 1) then 1
		else 0
	end as IS_L_DAY_OF_YEAR,--是否当年最后一天
	case 
		when TRUNC(sysdate) = trunc(sysdate, 'q') then 1
		else 0
	end as IS_F_DAY_OF_QUARTER,--是否当季第一天
	case 
		when TRUNC(sysdate) = (trunc(add_months(sysdate,3), 'Q') -1) then 1
		else 0
	end as IS_L_DAY_OF_QUARTER,--是否当季最后一天
	case 
		when TRUNC(sysdate) = trunc(sysdate, 'mm') then 1
		else 0
	end as IS_F_DAY_OF_MONTH,--是否当月第一天
	case
		when TRUNC(sysdate) = TRUNC (LAST_DAY (SYSDATE)) then 1
		else 0
	end as IS_L_DAY_OF_MONTH,--是否当月最后一天
	case 
		when TRUNC(sysdate) = TRUNC(sysdate,'iw') then 1
		else 0
	end as IS_F_DAY_OF_WEEK,--是否当周第一天
	case
		when TRUNC(sysdate) = (TRUNC(sysdate,'iw')+6) then 1
		else 0
	end as IS_L_DAY_OF_WEEK,--是否当周最后一天
	case 
		when MOD(extract(year from sysdate),4) = 0 then 1
		else 0
	end as IS_LEAP--是否闰年
from dual;

 

本文地址：https://blog.csdn.net/Hjchidaozhe/article/details/108977247