|
create or replace function FUNC_GET_DATE_BY_WEEK
(
theYearWeek IN VARCHAR2
)
return date is
normalDate date;
--根据四位年周获取当周一的日期
errorMsg VARCHAR2(500);
strLength number;
yearFirstDay date;
daySeqInWeek number;
firstDayInFirstWeek date;
strWeek number;
begin
errorMsg:= ‘‘;
SELECT LENGTH(theYearWeek) INTO strLength FROM DUAL;
IF strLength = 4 THEN
--获取当年第一天
SELECT TO_DATE(SUBSTR(theYearWeek,2)||‘0101‘,‘YYMMDD‘) INTO yearFirstDay FROM DUAL;
ELSE
errorMsg:=‘输入格式不正确，无法转换‘;
SELECT TRUNC(SYSDATE) INTO normalDate FROM DUAL;
RETURN normalDate;
END IF;
--------------------------------------
---获取当年第一天所在周周一的日期
--------------------------------------
--获取当年第一天是所在周的第几天
SELECT SUBSTR(TO_CHAR(yearFirstDay,‘IWD‘),LENGTH(TO_CHAR(yearFirstDay,‘IWD‘)),1) INTO daySeqInWeek FROM DUAL;
IF daySeqInWeek = 1 THEN
--因为oracle周从周日算起，所以如果是周的第一天要加一才是周一的日期
SELECT yearFirstDay + 1 INTO firstDayInFirstWeek FROM DUAL;
ELSE
--非第一天则减
SELECT yearFirstDay - (daySeqInWeek - 2) INTO firstDayInFirstWeek FROM DUAL;
END IF;
SELECT SUBSTR(theYearWeek,3,2) INTO strWeek FROM DUAL;
--当年第一天所在周周一的日期加上周数即是所求
SELECT firstDayInFirstWeek + 7 * (strWeek - 1) INTO normalDate FROM DUAL;
return(normalDate);
end;
参考：https://www.2cto.com/database/201401/269429.html（oracle周相关概念）
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
