只需要使用CASE语句进行比较。

SELECT CASE
         WHEN TRIM(TO_CHAR(dt, 'DAY')) = 'SUNDAY' THEN dt
         ELSE NEXT_DAY(dt, 'SUN')
       END sunday
FROM   (SELECT NVL(TO_DATE(:1, 'DD-MON-YYYY'), '31-DEC-9999') dt
        FROM   dual);

从你的日期中减去1天，然后使用NEXT_DAY：

SELECT NEXT_DAY(
         NVL(
           TO_DATE( :1, 'DD-MON-YYYY' ) - INTERVAL '1' DAY,
           DATE '9999-12-31'            -- Use a date literal
         ),
         'SUN'
       )
FROM   DUAL

在讨论答案之前，我想解决你的方法的两个依赖性。

First-取决于默认的日期格式

NVL(TO_DATE(:1,'DD-MON-YYYY'),'31-DEC-9999')

NVL的第一个参数是date，第二个是要转换为DATE的String

其次在查询中使用“SUN”取决于NLS设置。如果客户端具有不同的NLS_LANGUAGE设置，查询将失败。尝试与

ALTER SESSION SET NLS_LANGUAGE= 'GERMAN';

这个公式以NLS独立的方式返回下一个星期日：

my_date + 6 - (trunc(my_date) - trunc(my_date,'IW'))

解释

trunc(my_date) - trunc(my_date,'IW')

返回0到6之间的数字。周一零到周日六点。

6 - (trunc(my_date) - trunc(my_date,'IW'))

返回一个介于6和0之间的数字。星期一六点到星期天零点。这意味着这是到下个星期天的天数。简单地将它添加到你的日期参数，它将在下个星期天返回。（星期天，你加零，即。正如你所期望的那样，你会在同一天到达。

测试

with my_dates as (
select  TO_DATE('28-10-2017','DD-MM-YYYY') + rownum -1 my_date  from DUAL connect by level <= 10 )

select my_date, trunc(my_date) - trunc(my_date,'IW')  day_of_week,
    6 - (trunc(my_date) - trunc(my_date,'IW')) days_to_next_sunday,
    -- next sunday formula
    my_date + 6 - (trunc(my_date) - trunc(my_date,'IW'))   next_sunday       
from my_dates;

MY_DATE   DAY_OF_WEEK DAYS_TO_NEXT_SUNDAY NEXT_SUNDAY
--------- ----------- ------------------- -----------
28-OCT-17           5                   1 29-OCT-17   
29-OCT-17           6                   0 29-OCT-17   
30-OCT-17           0                   6 05-NOV-17   
31-OCT-17           1                   5 05-NOV-17   
01-NOV-17           2                   4 05-NOV-17   
02-NOV-17           3                   3 05-NOV-17   
03-NOV-17           4                   2 05-NOV-17   
04-NOV-17           5                   1 05-NOV-17   
05-NOV-17           6                   0 05-NOV-17   
06-NOV-17           0                   6 12-NOV-17

因此，您的查询将如下

SELECT  
   my_date + 6 - (trunc(my_date) - trunc(my_date,'IW'))   next_sunday 
FROM   (SELECT TO_DATE(NVL(:1, '31-DEC-9999'), 'DD-MON-YYYY') my_date
        FROM   dual);

要使用WHERE子句中的表达式，只需使用一个子查询分解，其中一行包含参数date并将其交叉连接到表中：

with my_date as (SELECT TO_DATE(NVL(:1, '31-DEC-9999'), 'DD-MON-YYYY') my_date FROM   dual)
select * from my_tab d cross join my_date 
where D.CAL_DATE <= my_date + 6 - (trunc(my_date) - trunc(my_date,'IW'));

这是我使用的一个函数。它可以被修改为接受一个开始日期参数：

CREATE OR REPLACE FUNCTION nextweekday (
  dayofweek number -- 0 is sunday, 6 is saturday
)
-- this function will return the date of the next requested weekday. If the current date is the requested weekday, it will return the current date.
-- If you want the weekday after the next weekday then use values greater than 6 (ie. add 7 for each week further into the future).
-- If you want the previous weekday then subtract 14 from your value and then 7 for each preceding week before that
RETURN DATE
IS
    sundayNumber NUMBER; -- for what the DB says is the number for a Sunday - this will depend on NLS settings for this DB
    dow NUMBER; -- the dayofweek parameter converted to the number of the week the DB wants - this will depend on NLS settings for this DB
    todayNumber NUMBER; -- the day of the week of the current date
    daysToAdd NUMBER; -- number of days to add to the current date to get to the next required week day
BEGIN
    -- get the day number of a known Sunday and the current date
    SELECT TO_NUMBER(TO_CHAR(TO_DATE('2023/09/17', 'YYYY/MM/DD'), 'D')), TO_NUMBER(TO_CHAR(sysdate, 'D')) INTO sundayNumber, todayNumber
    FROM dual;

 

    -- convert the passed parameter to the DB's equivilent
    dow := dayofweek + sundayNumber;
    IF dow > 7 THEN
        dow := dow - 7;
    END IF;

 

    -- calculate the number of days to add to current date
    daysToAdd := dow - todayNumber;
    IF daysToAdd < 0 THEN
        daysToAdd := daysToAdd + 7;
    END IF;

 

    -- return the date of the requested week day
    RETURN sysdate + daysToAdd;
END;