Forum Stats

  • 3,875,416 Users
  • 2,266,914 Discussions


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

Albert Chao
Albert Chao Member Posts: 207 Red Ribbon
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.

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.



  • Stax
    Stax Member Posts: 184 Silver Badge

    hi, Albert Chao

    SQL> ed
    Wrote file afiedt.buf
      1  with test(id ,col) as (
      2  select 1,TO_TIMESTAMP('2022-11-09 06:14:00.742000000', 'YYYY-MM-DD HH24:MI:SS.FF') from dual union all
      3  select 2,TO_TIMESTAMP('2022-11-07 09:14:00.742000000', 'YYYY-MM-DD HH24:MI:SS.FF') from dual union all
      4  select 1,TO_TIMESTAMP('2022-11-06 06:14:00.742000000', 'YYYY-MM-DD HH24:MI:SS.FF') from dual
      5  )
      6  select t.*
      7  ,trunc(col,'IW') Monday
      8*  from test t
    SQL> /
            ID COL                            MONDAY
    ---------- ------------------------------ -------------------
             1 09.11.2022 06:14:00.742000000  07.11.2022 00:00:00
             2 07.11.2022 09:14:00.742000000  07.11.2022 00:00:00
             1 06.11.2022 06:14:00.742000000  31.10.2022 00:00:00
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,780 Red Diamond
    edited Nov 9, 2022 1:27PM

    Hi, @Albert Chao

    So, if col is 8:00 or earlier on a Monday, then you want to return col itself; for all other cases you want to return the same time (including fractions of a second) on the following Monday. Is that right?

    Here's one way to do that:

    SELECT    id
    ,	  col
    ,	  col + NUMTODSINTERVAL ( 7 + TRUNC ( col - INTERVAL '8' HOUR
    					    , 'IW'
    	      			    - TRUNC (col)
    				, 'DAY'
    				)  AS monday
    FROM	  test

    This adds between 7 and 14 days to the previous Monday.

    If col is exactly 8:00.00.000000000 on a Monday, the query above adds 7 days. Is that okay?