文章为转载,之后找不到原博地址了,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 ;
BEGINSELECT
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 ;