Oracle 获取当前日期是月的第几周

 函数FUNC_GET_WEEKOFMONTH： IW 是年的自然周； WW是年的第一天起开始算7天为一周

FUNCTION FUNC_GET_WEEKOFMONTH (V_PSD DATE)
RETURN NUMBER
IS
N_WEEK NUMBER (11) := 0;
BEGIN
SELECT TO_CHAR (V_PSD, 'IW') - TO_CHAR (TRUNC (V_PSD, 'MM'), 'IW') + 1 INTO N_WEEK FROM DUAL;

RETURN NVL (N_WEEK, 0);
EXCEPTION
WHEN OTHERS
THEN
LOGGER.ERROR (SUBSTR (SQLERRM, 1, 512) || ',Error');
RETURN 0;
END FUNC_GET_WEEKOFMONTH;

 

获取当前日期所在月的每一周的周一~周日，结果如下图：

代码：

PROCEDURE PRC_Z_WEEK_CONFIG(CUR_DATE IN VARCHAR2, EXITCODE OUT NUMBER) AS
/******************************************************************************
NAME:
documnet:

REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------

******************************************************************************/
GV_LOGIC_NAME VARCHAR2(40) := 'Z_WEEK_CONFIG;';

V_MON_FIRST DATE;
V_MON_CUR DATE;
V_MON_END DATE;
V_MON_DAY DATE;
V_SUN_DAY DATE;
V_FLAG VARCHAR(2);
V_WEEK_NO INTEGER;
BEGIN
EXITCODE := -1;
LOGGER.INFO(GV_LOGIC_NAME || ':' || ' ' || ' Start');

if CUR_DATE is null then
V_MON_CUR := V_SYSDATE;
else
V_MON_CUR := to_date(CUR_DATE, 'yyyymmdd');
END IF;

V_MON_FIRST := LAST_DAY(ADD_MONTHS(V_MON_CUR, -1)) + 1; -- the first day of month
V_MON_END := LAST_DAY(V_MON_CUR); -- the last day of month
V_MON_DAY := V_MON_FIRST; -- monday : the first day of month is monday too
V_SUN_DAY := V_MON_FIRST + (8 - TO_NUMBER(TO_CHAR(V_MON_FIRST, 'd'))); -- sunday : the end day of month is sunday too
V_FLAG := 'N';
V_WEEK_NO := 1;

DELETE FROM z_week_config;

while v_mon_end > v_sun_day loop

if V_MON_DAY = trunc(V_MON_DAY, 'd') then
v_sun_day := V_MON_DAY;
else
v_sun_day := v_mon_day + (8 - to_number(to_char(v_mon_day, 'd')));
end if;

if v_sun_day > v_mon_end then
v_sun_day := v_mon_end;
END IF;

if v_mon_cur >= v_mon_day and v_mon_cur <= v_sun_day then
v_flag := 'Y';
END IF;

insert into z_week_config
(version_day, mon_day, sun_day, icon, week_no, flag)
values
(v_mon_cur,
v_mon_day,
v_sun_day,
to_char(v_mon_day, 'mm/dd') || '~' || to_char(v_sun_day, 'mm/dd'),
'WEEK' || v_week_no,
v_flag);

v_mon_day := v_sun_day + 1;
v_week_no := v_week_no + 1;

end loop;
COMMIT;

----- if this month is not enough 6 weeks,then add to 6 weeks
while v_week_no <= 6 loop

insert into z_week_config
(version_day, week_no, flag)
values
(v_mon_cur, 'WEEK' || v_week_no, 'N');

v_week_no := v_week_no + 1;

end loop;

COMMIT;

LOGGER.INFO(GV_LOGIC_NAME || ':' || '' || ',Success');
EXITCODE := 0;
EXCEPTION
WHEN OTHERS THEN
EXITCODE := SQLCODE;
LOGGER.ERROR;
END PRC_Z_WEEK_CONFIG;