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;

CodePudding user response：

You needed "Dy" parameter for checking whether it's monday or not

The "col to_number(TRUNC(col, 'iw') 7 - trunc(col))" clause calculates (TRUNC(col, 'iw') 7 - trunc(col)) and adds number of days to make date to next monday

select 
  case 
  when to_char(col, 'Dy') = 'Mon' then
    case
    when col > (trunc(col)   8/24) then 
      col   7
    else col end
  else -- since it's not Mon, it lays between Tue and Sun
    col   to_number(TRUNC(col, 'iw')   7 - trunc(col))
  end
  from test

Here's db_fiddle to check

CodePudding user response：

If you look at the number of days between the value and the start of the week instead of using between then it's a bit simpler:

case
  when trunc(col) - trunc(col, 'IW') > 0 or extract(hour from col) >= 8
  then col   ((7 - (trunc(col) - trunc(col, 'IW'))) * interval '1' day)
  else col
end

If the number of days between today - trunc(col) - and the start of the week - trunc(col, 'IW') - is greater than zero then the value isn't from Monday; or it is zero but after 08:00:00; then add the appropriate number of days to take to to the following Monday - at the same time, which is what you showed, but seems a little odd.

As a demo with a couple of extra values to cover what you mentioned in the question:

select id, col,
  case
    when trunc(col) - trunc(col, 'IW') > 0 or extract(hour from col) >= 8
    then col   ((7 - (trunc(col) - trunc(col, 'IW'))) * interval '1' day)
    else col
  end as adjusted
from test;

fiddle

You said ">8am" and "time is after 8am" but I've assumed you actually meant "8am or later", so I've used hour >= 8. If you didn't mean that - so you did really mean that 08:00:00 is not adjusted but 08:00:01 is - then that would again seem odd, but you can check the other time components:

select id, col,
  case
    when trunc(col) - trunc(col, 'IW') > 0
      or extract(hour from col) > 8
      or extract(minute from col) > 0
      or extract(second from col) > 0
    then col   ((7 - (trunc(col) - trunc(col, 'IW'))) * interval '1' day)
    else col
  end as adjusted
from test;