SS113 SS113 - 7 months ago 8
SQL Question

SQL statement using AND depending on the day of the week

I currently have 2 different sql scripts that I need to run depending on the day of the week. I'm trying to find a way to combine them into one and have logic to check what day of the week it is and run the appropriate changes based on that.

So the basic structure is:

SELECT * FROM table
WHERE foo = 'bar'
AND foo2 = 'bar2'
AND foo3 = 'bar3';


The only difference is if it's a Monday, one of the AND cases needs to change.

So the pseudo code I need looks something like:

SELECT * FROM table
WHERE foo = 'bar'
AND foo2 = 'bar2'

if day = 'Monday' then
AND foo3 = 'bar3'
else
AND foo3 = 'bar3a';


Any ideas? I tried to incorporate a Case Select based on this but didn't work out for me.

Thanks!

Answer

I think the correct logic is:

WHERE foo = 'bar' AND
      foo2 = 'bar2'
      ( (TO_CHAR(SYSDATE, 'Dy') = 'Mon' AND foo3 = 'bar3') ) OR
        (TO_CHAR(SYSDATE, 'Dy') <> 'Mon' AND foo3 = 'bar3a')
      )

Two important considerations:

  • This checks both for Monday and not-Monday.
  • This uses date abbreviations rather than the full name. Oracle pads date names so the result is not 'Monday' but 'Monday ' and the spaces can make a difference.