How to check for a specific date with a CASE statement in SQL in Oracle SQL

Question

I am trying to write a statement in OracleSQL which will select the results from either yesterday or the last 3 days according to whether the date in a specific field (column3) is monday.

SELECT Column1, Column2, Column3 FROM Table
WHERE CASE
       WHEN To_Char(Column2, 'Day') = 'Monday'
          THEN Column3 >= (SYSDATE - Interval "3" Day)
       ELSE Column3 >= (SYSDATE - Interval "1" Day)
    END
Order by Column3 DESC; 

alternatively

SELECT Column1, Column2, Column3 FROM Table
WHERE Column3 >= 
    (CASE
       WHEN To_Char(Current_Date 'D') = 1
          THEN To_Char(Current_Date - 3)
       ELSE To_Char(Current_Date - 1)
    END
Order by Column3 DESC; 

For clarity, I have a table with many thousands of entries and I need to display only recent results from yesterday or from the weekend. The snippet is meant to read the date of entry from column3 and then either show the last 3 days or yesterday accordingly.

SOLVED: The ultimate code which worked--

SELECT Column1, Column2, Column3 FROM The_Table
WHERE Column3 >= 
    CASE
        WHEN to_char(Current_Date, 'fmDay', 'nls_date_language = English') = 'Monday'
            THEN TRUNC(SYSDATE) - INTERVAL '3' DAY
        ELSE TRUNC(SYSDATE) - INTERVAL '1' DAY
    END
ORDER BY Column3 DESC;

Answer 1

Something like this?

  SELECT column1, column2, column3
    FROM your_table
   WHERE column3 >=
         CASE
            WHEN TO_CHAR (column2, 'fmDay', 'nls_date_language = english') = 'Monday'
            THEN
               TRUNC(SYSDATE) - INTERVAL '3' DAY
            ELSE
               TRUNC(SYSDATE) - INTERVAL '1' DAY
         END
ORDER BY column3 DESC;

• in TO_CHAR, include fm (otherwise you'll get value which is right-padded with spaces, up to the max day name length) and add language identifier

  SQL> select 'x' || to_char(sysdate + 1, 'Day') || 'x' result from dual;
  
  RESULT
  -------------
  xUtorak     x
  
  SQL> select 'x' || to_char(sysdate + 1, 'fmDay', 'nls_date_language = english') || 'x' result from dual;
  
  RESULT
  -----------
  xTuesdayx
  

• truncate sysdate to set it to midnight; otherwise, you'll get time component as well. I guess you don't need it

  SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';
  
  Session altered.
  
  SQL> select sysdate - interval '3' day result1,
    2   trunc(sysdate)- interval '3' day result2
    3  from dual;
  
  RESULT1             RESULT2
  ------------------- -------------------
  12.05.2023 09:45:46 12.05.2023 00:00:00
  

Answer 2

As per this answer, a locale independent way of checking for the day of week, is using the ISO week:

SELECT
   Column1,
   Column2,
   Column3
FROM Table
WHERE
   CASE
      WHEN TRUNC(SYSDATE) - TRUNC(SYSDATE, 'IW') = 0
      THEN Column3 >= (SYSDATE - Interval "3" Day)
      ELSE Column3 >= (SYSDATE - Interval "1" Day)
   END
ORDER BY Column3 DESC;