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').
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').
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
Also asked on stack overflow.