文章为转载,之后找不到原博地址了,sorry
to_week_by_year(to_date('2015-1-1', 'yyyy-MM-dd'))
CREATE OR REPLACE FUNCTION to_week_by_year (sdate IN DATE) RETURN NUMBER IS Result NUMBER ; BEGIN SELECT TO_CHAR ( DECODE ( SIGN ( ( TO_DATE ( TO_CHAR (sdate, 'yyyymmdd'), 'yyyymmdd' ) + TO_NUMBER ( DECODE ( TO_CHAR ( TRUNC ( TO_DATE ( TO_CHAR (sdate, 'yyyymmdd'), 'yyyymmdd' ), 'yyyy' ), 'd' ), '1', '8', TO_CHAR ( TRUNC ( TO_DATE ( TO_CHAR (sdate, 'yyyymmdd'), 'yyyymmdd' ), 'yyyy' ), 'd' ) ) ) - 2 ) - LAST_DAY ( TO_DATE ( TO_CHAR (sdate, 'yyyymmdd'), 'yyyymmdd' ) ) ), 1, LAST_DAY ( TO_DATE ( TO_CHAR (sdate, 'yyyymmdd'), 'yyyymmdd' ) ), ( TO_DATE ( TO_CHAR (sdate, 'yyyymmdd'), 'yyyymmdd' ) + TO_NUMBER ( DECODE ( TO_CHAR ( TRUNC ( TO_DATE ( TO_CHAR (sdate, 'yyyymmdd'), 'yyyymmdd' ), 'yyyy' ), 'd' ), '1', '8', TO_CHAR ( TRUNC ( TO_DATE ( TO_CHAR (sdate, 'yyyymmdd'), 'yyyymmdd' ), 'yyyy' ), 'd' ) ) ) - 2 ) ), 'ww' ) INTO result FROM dual ; RETURN Result ; END to_week_by_year ;