CREATE OR REPLACE FUNCTION WEEK_NO (CDATE VARCHAR2) RETURN NUMBER AS
V_DATE VARCHAR2(08);
FDATE DATE;
DNUM NUMBER;
BEGIN
V_DATE:=CDATE;
--
SELECT DECODE(TO_CHAR(TO_DATE(SUBSTR(V_DATE,1,4)||'0101','YYYYMMDD'),'D'),
1,7,
2,6,
3,5,
4,4,
5,3,
6,2,
7,1)
+ TO_DATE(SUBSTR(V_DATE,1,4)||'0101','YYYYMMDD')
INTO FDATE
FROM DUAL;
--
SELECT TO_DATE(V_DATE,'YYYYMMDD') - FDATE
INTO DNUM
FROM DUAL;
--
IF DNUM<0 THEN
RETURN 1;
ELSE
RETURN TRUNC(DNUM/7) +2;
END IF;
END;
V_DATE VARCHAR2(08);
FDATE DATE;
DNUM NUMBER;
BEGIN
V_DATE:=CDATE;
--
SELECT DECODE(TO_CHAR(TO_DATE(SUBSTR(V_DATE,1,4)||'0101','YYYYMMDD'),'D'),
1,7,
2,6,
3,5,
4,4,
5,3,
6,2,
7,1)
+ TO_DATE(SUBSTR(V_DATE,1,4)||'0101','YYYYMMDD')
INTO FDATE
FROM DUAL;
--
SELECT TO_DATE(V_DATE,'YYYYMMDD') - FDATE
INTO DNUM
FROM DUAL;
--
IF DNUM<0 THEN
RETURN 1;
ELSE
RETURN TRUNC(DNUM/7) +2;
END IF;
END;