这个oracle function 怎么写?(日期转换相关的)
实现如下功能,每周4---下周3的为一周,如果碰到年初或年终则自动截断,如传入某个日期,即可返回它属于该年的第几周?比如2008-01-02 返回第1周,2009-12-31返回第53周
2008-01-01----2008-01-02---第1周
2008-01-03----2008-01-09---第2周
2008-01-03----2008-01-09---第3周
....2008-12-04----2008-12-10---第50周
2008-12-11----2008-12-17---第51周
2008-12-18----2008-12-24---第52周
2008-12-25----2008-12-31---第53周...
2009-12-24----2009-12-30---第52周
2009-12-31----2009-12-31---第53周
....
RETURN NUMBER
IS
WEEK NUMBER;
BEGIN
SELECT TO_CHAR(TO_DATE(S,'YYYY-MM-DD'),'FMWW') INTO WEEK FROM DUAL;
RETURN WEEK;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END;
from
(
select to_date('2009-01-01','YYYY-MM-DD HH24:MI:SS')+rownum-1 as dtime,
to_char(to_date('2009-01-01','YYYY-MM-DD HH24:MI:SS')+rownum-1,'ww') as dweek
from dual
connect by rownum<=365
) aa
where to_char(dtime,'yyyy')=to_char(sysdate,'yyyy')
group by dweek
order by dweek
例如:
select to_char(sysdate,'WW') from dual;