CODEWITHSUNDEEP
oracle-databasecasewhere-clause
royskatt
royskatt

Reputation: 1210

Oracle - complexer case statement in where-clause

In Oracle I need to set the where-clause of MYDATE depended on the month of the current year. If month is equal or greater than April, then restrict MYDATE to the current year (01.01.YEAR - 31.12.YEAR) ELSE restrict MYDATE to every date older then the current time.

I'd need a pure SQL solution, not PL/SQL if possible. I tried to adapt what I found in the net and at stackoverflow.com, but the syntax is wrong:

SELECT text, mydate
FROM mytable
WHERE
CASE
    WHEN to_number(TO_CHAR(sysdate, 'MM')) >= 4 -- if month of current year >= april
        THEN mydate >=TRUNC(LAST_DAY(SYSDATE), 'YYYY') --beginning of current year
        AND mydate  < TRUNC(LAST_DAY(SYSDATE)+1, 'YYYY') -- end of current year
    ELSE -- if month of current year < april
        mydate < sysdate;

Could anyone help me out here?

Thank you very much in advance.

Upvotes: 0

Views: 145

Answers (1)

Alen Oblak
Alen Oblak

Reputation: 3325

You don't need a CASE. Just AND / OR in the WHERE will do.

SELECT text, mydate
FROM mytable
WHERE
   (     to_number(TO_CHAR(sysdate, 'MM')) >= 4 -- if month of current year >= april
     AND mydate >=TRUNC(LAST_DAY(SYSDATE), 'YYYY') --beginning of current year
     AND mydate < TRUNC(LAST_DAY(SYSDATE)+1, 'YYYY') -- end of current year
   ) OR (
         to_number(TO_CHAR(sysdate, 'MM')) < 4 
    AND  mydate < sysdate)
   );

Upvotes: 2

Related Questions