CODEWITHSUNDEEP
sqloracle-databasewhere-clause
machump
machump

Reputation: 1267

Oracle SQL - change WHERE condition depending on current_date

I have a table similar to

TradeDate        | Widgets
12-DEC-2020      6
13-DEC-2020      3
13-DEC-2020      9
14-DEC-2020      3
15-DEC-2020      12
15-DEC-2020      4
...
08-JAN-2020      11
...

I want to write a daily query that returns a result set depending on today's date. If today's date is 25-DEC-2020 or 01-JAN-2020 I want to return rows where TradeDate is today's date or 1 day later.

TradeDate        | Widgets
25-DEC-2020      26
25-DEC-2020      13
26-DEC-2020      19
26-DEC-2020      34

Otherwise, if the date is not Dec 25, Jan 1, or another specified date, I'd only want to retrieve records with the current date. If today's date was Dec 13, then the results would show:

    TradeDate        | Widgets
    13-DEC-2020      3
    13-DEC-2020      9

I thought I was onto something using CASE in the WHERE:

SELECT TradeDate, Widgets FROM table
WHERE 
CASE WHEN (current_date like '25-DEC%' OR current_date like '01-JAN%') 
  THEN (TradeDate = current_date or TradeDate = current_date +1 )
  ELSE TradeDate = Current_date

But this does not work. Is there a simple way to achieve what I am seeking? Dynamic WHERE conditions?

Upvotes: 0

Views: 291

Answers (2)

Popeye
Popeye

Reputation: 35910

You can use OR as follows:

SELECT TradeDate, Widgets
  FROM table
 WHERE (to_char(sysdate,'dd-MON') in ('25-DEC','01-JAN')
         AND TRADEDATE >= TRUNC(SYSDATE) 
         AND TRADEDATE < TRUNC(SYSDATE) + 2
        )
        OR TRADEDATE = TRUNC(SYSDATE)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can use:

where tradeDate >= trunc(sysdate) and
      tradeDate < trunc(sysdate) + interval '2' day

Upvotes: 1

Related Questions