函数:自定义日历周获取周数
功能说明:计算某日期是一年的第几周,支持自定义日历周,将任意周内星期设为一周起始日。
参数说明:currentDate为目标日期;weekStandard为自定义一周开端,可将任意周一到周日设置为一周的开头,参数取值范围为
1~7,周一用1表示,周二用2表示,以此类推。
CREATE OR REPLACE function GetWeekNum(currentDate IN date,weekStandard IN varchar2) return varchar2
as
firstDayWeekNo NUMBER; --本年1号周几(以日历为基础,周一为第一天)
fisrtWeekDayCount NUMBER; --本年第一周天数(以weekNo为标准)
dayCount NUMBER; --总天数
weekCount NUMBER; --总周数
begin
if currentDate is null or weekStandard is null or weekStandard < 1 or weekStandard > 7
then
return null;
end if;
firstDayWeekNo := to_char(trunc(currentDate,'y'),'d','NLS_DATE_LANGUAGE=AMERICAN')-1;
if firstDayWeekNo = 0
then
firstDayWeekNo := 7;
end if;
if weekStandard = firstDayWeekNo
then
if(currentDate = trunc(currentDate,'y'))
then
fisrtWeekDayCount := 1;
else
fisrtWeekDayCount := 7;
end if;
elsif weekStandard < firstDayWeekNo
then
fisrtWeekDayCount := 7-(firstDayWeekNo-weekStandard);
elsif weekStandard > firstDayWeekNo
then
fisrtWeekDayCount := (weekStandard - firstDayWeekNo);
end if;
dayCount := to_char(currentDate,'ddd');
weekCount := ceil((dayCount-fisrtWeekDayCount)/7)+1;
return to_char(currentDate,'YYYY')||'W'||to_char(weekCount,'FM00');
end;
案例:把周五设置为一周的开始。20220107才会变为下一周。