Article for reprint, and then can not find the original Bo address, 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;