Oracle中IW参数无法返回周起始日及CASE逻辑表达式问题求助

测试数据 #

create table test(id number,col timestamp(6));
insert into test values(1,TO_TIMESTAMP('2022-11-09 06:14:00.742000000', 'YYYY-MM-DD HH24:MI:SS.FF'));
insert into test values(2,TO_TIMESTAMP('2022-11-07 09:14:00.742000000', 'YYYY-MM-DD HH24:MI:SS.FF'));

数据库：Oracle Live

需求 #

• 若col的日期为周二至周日，返回下周一的对应时间（例如：2022-11-09 06:14:00.742000000 对应 2022-11-14 06:14:00.742000000）
• 若col的日期为周一且时间大于上午8点，返回下下周周一的对应时间（例如：2022-11-14 09:14:00.742000000 对应 2022-11-21 09:14:00.742000000）

问题排查 #

你提到trunc(col,'IW')未返回周一，这个函数在Oracle中是返回ISO周的周一，可能是会话的NLS_TERRITORY参数影响了星期显示，可先执行以下语句验证：

select col, trunc(col, 'IW') as iso_week_start from test;

最终实现SQL #

select 
    id,
    col,
    case
        -- 周二至周日（ISO周中对应2-7）：加7天到下周一，保留原时间部分
        when to_char(col, 'IW') between '2' and '7' then
            trunc(col, 'IW') + interval '7' day + (col - trunc(col))
        -- 周一且时间>=8点：加14天到下下周周一，保留原时间部分
        when to_char(col, 'IW') = '1' and col >= trunc(col) + interval '8' hour then
            trunc(col, 'IW') + interval '14' day + (col - trunc(col))
        -- 周一且时间<8点：返回原时间（需求未明确，可根据实际调整）
        else
            col
    end as target_datetime
from test;

代码说明 #

• trunc(col, 'IW')：获取当前日期所属ISO周的起始日（周一）
• col - trunc(col)：提取col中的时间部分（时分秒及毫秒），确保返回的时间与原时间一致
• to_char(col, 'IW')：返回1（周一）到7（周日），不受地域参数影响，判断星期几更可靠
• interval '7' day / interval '14' day：快速计算下周一/下下周周一的日期

内容的提问来源于stack exchange，提问作者Vicky