IW parameter not returning start of week and not able to handle logical expression using 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'));

Database: Oracle Live

I have a requirement where I need to check with weekdays by comparing the date timestamp column.

  1. 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.

select 
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;


Comments

Popular posts from this blog

Spring Elasticsearch Operations

Network Error and Timeout on Authorize.net JS

Object oriented programming concepts (OOPs)