Boolean expression in CASE in WHERE clause does not work

Question

I am having problems with Firebird SQL statement in version Firebird 2.5.

Based on today's date, I have to select either this month's data or the previous month's data.

SELECT * FROM FA_DOBAVNICA
WHERE
1=1

AND CASE WHEN
    extract(day from cast('Now' as date)) < 9
THEN
   DATUM_NAROCILA BETWEEN 'start of previous month' AND 'end of previous month'
ELSE
   DATUM_NAROCILA BETWEEN 'start of this month' AND 'end of this month'
END 

I am getting a 104 error Token unknown for BETWEEN. I have no idea what am I doing wrong.

Answer 1

If I understood your problem correctly you can rephrase it with an or clause like this and it should do the job:

SELECT * FROM FA_DOBAVNICA
WHERE
1=1
AND 
((extract(day from cast('Now' as date)) < 9 AND DATUM_NAROCILA BETWEEN 'start of previous month' AND 'end of previous month')
or
(extract(day from cast('Now' as date)) > 8 AND DATUM_NAROCILA BETWEEN 'start of this month' AND 'end of this month'))