oracle判断为周一_oracle根据四位年周取当周周一的日期函数

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,0,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;

标签：第一天,INTO,yearFirstDay,周取,四位,DUAL,normalDate,oracle,SELECT

来源： https://www.cnblogs.com/haizine/p/10628246.html