Forum Stats

  • 3,875,416 Users
  • 2,266,914 Discussions
  • 7,912,203 Comments

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.

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;


Also asked on stack overflow.

Tagged:

Answers

  • 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
    
    
    SQL>
    
  • 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?