SELECT TO_CHAR(ADD_MONTHS(SYSDATE,+7),'IW')
  FROM dual

或者像你这样，

SELECT date_id,TO_CHAR(ADD_MONTHS(TO_DATE(date_id,'YYYYMMDD'),+7),'IW') week_of_year
  FROM date_dim

如果你不喜欢ISO日期法，即第一周是从星期天开始的一周，而星期天是一年中的第一天，你可以试着通过将它移回到前一个星期天（TRUNC（..，'D'），然后提前一周，再加上7个月来抵消它。

SELECT date_id, TO_CHAR(ADD_MONTHS(TRUNC(date_id,'D') + 7,7),'IW') week_of_year
  FROM date_dim

你对你的日期格式感到困惑（“yyyymmdd”与“DD-Mon-YY”），所以我在回答中使用了一个真实的日期（mydate）。将你的字符串或数字转换成正确的日期，你就可以得到：-）
最重要的是检查你的日期是否〉= 6月1日。一旦完成了这一步，你就可以减去当年的6月1日或前一年的6月1日。嗯，差不多：-）

select 
  mydate,
  to_char(mydate, 'DY') as tag,
  trunc
  (
    case when to_char(mydate, 'mmdd') >= '0601' then
      trunc(mydate + 1, 'iw') + 6 - to_date(to_char(mydate, 'yyyy') || '0601', 'yyyymmdd')
    else
      trunc(mydate + 1, 'iw') + 6 - to_date(to_char(extract(year from mydate) - 1) || '0601', 'yyyymmdd')
    end / 7
  ) + 1 as fiscal_week
from ...
order by mydate;

演示：https://dbfiddle.uk/N5pX_5cV

更新的答案

你可以试试这个-提供日期像31-MAY-19开始你的日历从01-JUN-19和定义多少天你想要的。有一个5个月的转变，因为代码是从我的常规日历和调整开始的一周到星期日...测试OK的期间长达2年每次运行。所以如果你想要6年，你必须运行这3次...

WITH 
        base_calendar AS
            (
                SELECT CurrDate         AS Day_ID,
                TO_CHAR(CurrDate,'Day') AS Week_Day_Full,
                TO_CHAR(CurrDate,'DY')  AS Week_Day_Short,
                TO_NUMBER(TRIM(leading '0' FROM TO_CHAR(CurrDate,'D'))) AS Day_Num_of_Week,
                --
                MAX(CASE WHEN To_Char(CurrDate, 'ddmm') = '2902' THEN EXTRACT(YEAR From CurrDate) END) OVER(Order By CurrDate Rows Between Unbounded Preceding And Current Row) AS last_leap_year,
                Count(CASE WHEN (   TO_CHAR(CurrDate,'DY') = 'SUN' And 
                                    CurrDate Between To_date('01.06.' || To_Char(EXTRACT(YEAR From CurrDate)), 'dd.mm.yyyy') And To_date('31.05.' || To_Char(EXTRACT(YEAR From CurrDate) + 1), 'dd.mm.yyyy')  )
                                OR
                                 (   TO_CHAR(CurrDate,'DY') = 'SUN' And 
                                    CurrDate Between To_date('01.06.' || To_Char(EXTRACT(YEAR From CurrDate) - 1), 'dd.mm.yyyy') And To_date('31.05.' || To_Char(EXTRACT(YEAR From CurrDate)  ), 'dd.mm.yyyy')  )
                          THEN 1 
                      END) OVER(Order By CurrDate Rows Between Unbounded Preceding And Current Row) AS cnt_sundays
              FROM
                (
                    SELECT level n,
                        -- Calendar starts at the day after this date
                        TO_DATE('31/05/2019','DD/MM/YYYY') + NUMTODSINTERVAL(level,'DAY') CurrDate
                    FROM dual
                        -- Change for the number of days to be added to the table.
                    CONNECT BY level <= 731
                )
            )
SELECT  DISTINCT
      day_id,
      CASE  WHEN  (cnt_sundays + 1 ) - ( (EXTRACT(YEAR From Add_Months(Day_id, - 5)) -EXTRACT(YEAR From MIN(Day_id) OVER()) ) * 53) >= 54 THEN 1
            WHEN EXTRACT(YEAR From day_id) <> last_leap_year And day_id > = To_Date('01.06.' || To_Char(EXTRACT(YEAR From day_id)), 'dd.mm.yyyy' ) 
                THEN (cnt_sundays + 1)  - ( (EXTRACT(YEAR From Add_Months(Day_id, - 5)) -EXTRACT(YEAR From MIN(Day_id) OVER()) ) * 53) + 1
      ELSE (cnt_sundays + 1) - ( (EXTRACT(YEAR From Add_Months(Day_id, - 5)) -EXTRACT(YEAR From MIN(Day_id) OVER()) ) * 53) 
      END week_of_year,
      --
        week_day_full,
        week_day_short,
        CASE week_day_short   
            WHEN 'SUN' THEN 1
            WHEN 'MON' THEN 2
            WHEN 'TUE' THEN 3
            WHEN 'WED' THEN 4
            WHEN 'THU' THEN 5
            WHEN 'FRI' THEN 6
            WHEN 'SAT' THEN 7
    END AS day_num_of_week
FROM base_calendar
ORDER BY day_id

回复：

DAY_ID    WEEK_OF_YEAR WEEK_DAY_FULL WEEK_DAY_SHORT DAY_NUM_OF_WEEK
--------- ------------ ------------- -------------- ---------------
01-JUN-19            1 Saturday      SAT                          7 
02-JUN-19            2 Sunday        SUN                          1 
03-JUN-19            2 Monday        MON                          2 
04-JUN-19            2 Tuesday       TUE                          3 
05-JUN-19            2 Wednesday     WED                          4
... ...
... ...
13-SEP-19           16 Friday        FRI                          6 
14-SEP-19           16 Saturday      SAT                          7 
15-SEP-19           17 Sunday        SUN                          1 
16-SEP-19           17 Monday        MON                          2
... ...
30-DEC-19           32 Monday        MON                          2 
31-DEC-19           32 Tuesday       TUE                          3 
01-JAN-20           32 Wednesday     WED                          4 
02-JAN-20           32 Thursday      THU                          5 
...
27-FEB-20           40 Thursday      THU                          5 
28-FEB-20           40 Friday        FRI                          6 
29-FEB-20           40 Saturday      SAT                          7 
01-MAR-20           41 Sunday        SUN                          1 
02-MAR-20           41 Monday        MON                          2
...
29-MAY-20           53 Friday        FRI                          6 
30-MAY-20           53 Saturday      SAT                          7 
31-MAY-20            1 Sunday        SUN                          1 
01-JUN-20            1 Monday        MON                          2 
02-JUN-20            1 Tuesday       TUE                          3 
03-JUN-20            1 Wednesday     WED                          4 
04-JUN-20            1 Thursday      THU                          5 
05-JUN-20            1 Friday        FRI                          6 
06-JUN-20            1 Saturday      SAT                          7 
07-JUN-20            2 Sunday        SUN                          1
...  ...
...  ...
22-MAY-21           51 Saturday      SAT                          7 
23-MAY-21           52 Sunday        SUN                          1 
24-MAY-21           52 Monday        MON                          2 
25-MAY-21           52 Tuesday       TUE                          3 
26-MAY-21           52 Wednesday     WED                          4 
27-MAY-21           52 Thursday      THU                          5 
28-MAY-21           52 Friday        FRI                          6 
29-MAY-21           52 Saturday      SAT                          7 
30-MAY-21           53 Sunday        SUN                          1 
31-MAY-21           53 Monday        MON                          2 

 731 rows selected

.到2025年----上文评论中提到的结果的一部分.

... ...
29-MAY-25           53 Thursday      THU                          5 
30-MAY-25           53 Friday        FRI                          6 
31-MAY-25           53 Saturday      SAT                          7 
01-JUN-25            1 Sunday        SUN                          1 
02-JUN-25            1 Monday        MON                          2 
03-JUN-25            1 Tuesday       TUE                          3 
04-JUN-25            1 Wednesday     WED                          4 
05-JUN-25            1 Thursday      THU                          5 
06-JUN-25            1 Friday        FRI                          6 
07-JUN-25            1 Saturday      SAT                          7 
08-JUN-25            2 Sunday        SUN                          1 
... ...