Skip to Main Content


IW parameter not returning start of week and not able to handle logical expression using CASE

Albert ChaoNov 9 2022
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'));

Database: Oracle Live

I have a requirement where I need to check with weekdays by comparing the date timestamp column.
First I need to check if the col (For e.g 09-Nov-2022) lies between Tuesday & Sunday if it does then need to display next Monday date and time(14-Nov-2022 09:14:00.742000000').
2.If the col lies on Monday(14-Nov-2022) then need to check time if it is >8am then need to display next monday date and time(21-Nov-2022 09:14:00.742000000').
My attempt:
trunc(col,'IW') this is not returning the start week of the day i.e Monday but ideally it should return MOnday which is not coming in my attempt.

case when trunc(col) between trunc(col,'IW')+1 and trunc(col,'IW')+6 then --need to display next monday date & time
when trunc(col) = trunc(col,'IW') --and time is after 8am 
 then --need to display next monday date & time
from test;

Also asked on stack overflow.

Post Details
Added on Nov 9 2022