网上查询的方法都是这个:
SELECT TO_CHAR (p_date, 'W') INTO v_week FROM DUAL;
它实际上是星期N在当月第几次出现。
如本月的第1天是个周四,那么为个月的5,6,7号是第一次出现,它给出的就是第一周,实际上我们理解的应该是第二周。
/*+===============================================================
*计算日期是当月第几周
=================================================================+*/
FUNCTION fn_get_weekday (p_date DATE)
RETURN NUMBER
IS
v_week NUMBER;
v_mon_weekday NUMBER;
v_current_weekday NUMBER;
BEGIN
--当月1号是周几
SELECT DECODE (TO_CHAR (TRUNC (p_date, 'MON'), 'd') - 1, 0, 7, TO_CHAR (TRUNC (p_date, 'MON'), 'd') - 1) INTO v_mon_weekday FROM DUAL;
--当是周几
SELECT DECODE (TO_CHAR (p_date, 'd') - 1, 0, 7, TO_CHAR (p_date, 'd') - 1) INTO v_current_weekday FROM DUAL;
IF v_mon_weekday <= v_current_weekday
THEN
--月初星期数小于等于当前星期数,直接取
SELECT TO_CHAR (p_date, 'W') INTO v_week FROM DUAL;
--DBMS_OUTPUT.put_line (TO_CHAR (p_date, 'yyyy-mm-dd') || ' is week:' || v_week);
ELSE
--否则加1
SELECT TO_CHAR (p_date, 'W') + 1 INTO v_week FROM DUAL;
--DBMS_OUTPUT.put_line (TO_CHAR (p_date, 'yyyy-mm-dd') || ' is week:' || v_week);
END IF;
RETURN v_week;
END;