How to put Case in Where Statement for Oracle SQL

For the query below, I’m trying to pull a specific date range depending on the current day of the month. If it’s the 20th or less (e.g. “2/7/2020”) then I want the date range for January. Otherwise, I want the date range for February. Is it possible to be done with a case statement? Or there is a better way?

SELECT
    account,
    start_date,
    amount
FROM
    table1
WHERE
    CASE
            WHEN (
                SELECT
                    CAST(EXTRACT(DAY FROM sysdate) AS NUMBER)
                FROM
                    dual
            ) <= 20 THEN
                    start_date
            BETWEEN '2020-01-01' AND '2020-01-31'
        ELSE start_date BETWEEN '2020-02-01' AND '2020-02-29'
    END

Answer

You can do this by avoiding the case statement and using truncate the date – 20 to the month, e.g.:

SELECT account,
     start_date,
     amount
FROM   table1
WHERE  start_date >= TRUNC(SYSDATE - 20, 'mm')
AND    start_date < add_months(TRUNC(dt - 20, 'mm'), 1);

If you really had to use a CASE expression (you can’t use a CASE statement in SQL), you would need to do something like:

SELECT account,
     start_date,
     amount
FROM   table1
WHERE  start_date >= CASE WHEN to_char(SYSDATE, 'dd') <= '20' THEN add_months(TRUNC(SYSDATE, 'mm'), -1) ELSE TRUNC(SYSDATE, 'mm') END
AND    start_date < CASE WHEN to_char(SYSDATE, 'dd') <= '20' THEN TRUNC(SYSDATE, 'mm') ELSE add_months(TRUNC(SYSDATE, 'mm'), 1) END;

N.B. if you’re using a function, you don’t need to wrap it in a select .. from dual, you can use it directly in the SQL statement.

I’ve also assumed that you want a dynamic range, e.g. if the day of the month is 20 or less, the range is for the previous month, otherwise the current month.

---

ETA: You would use the above two queries if there is an index on the start_date column, otherwise you could simply do:

SELECT account,
     start_date,
     amount
FROM   table1
WHERE  TRUNC(start_date, 'mm') = TRUNC(SYSDATE - 20, 'mm');