case on where statement with date-CodePudding

I am trying to filter data on a date based on month. I went through different solutions in here but couldn't get it to work. I am trying to filter if month is less than march to use 2020 else get 2021 data.

I have tried 2 solutions based on different feed back:

select * 
from db.table1
where 
(case
(to_char(commit_date, 'mm')<'03') then 
(to_char(commit_date, 'mm')= (to_char(sysdate, 'mm')-1)
and to_char(commit_date, 'yyyy') = '2020') 
else 
(to_char(commit_date, 'mm')= (to_char(sysdate, 'mm')-1)
and to_char(commit_date, 'yyyy') = '2021') 
end)

This gives mme invalid relational operator.

Another solution

select * 
from db.table1

where 
(
(to_char(commit_date, 'mm')<'03' and to_char(commit_date, 'yyyy') = '2020') or (to_char(commit_date, 'yyyy') = '2021'))

doesn't seem to filter.

Thanks, Sam

CodePudding user response：

You can try to use TO_DATE function greater and equal than 2020-01-01 and smaller than 2020-03-01 or EXTRACT year equal 2021

SELECT * 
FROM db.table1
WHERE
  (commit_date >= TO_DATE('2020-01-01', 'yyyy-mm-dd') AND commit_date < TO_DATE('2020-03-01', 'yyyy-mm-dd'))
OR
  EXTRACT(YEAR FROM commit_date) = 2021