For selecting birthdays between two months where FROMDATE and TODATE are some parameters in a prepared statement I figured something like this:
select
p.id as person_id,
...
...
where e.active = 1
and extract(month from TODATE) >= extract(month from e.dateOfBirth)
and extract(month from e.dateOfBirth) >= extract(month from FROMDATE)
order by extract(month from e.dateOfBirth) DESC,
extract(day from e.dateOfBirth) DESC
How can this be improved to work with days as well?
At the end we picked litter different solution where we add fist create the anniversary date :
where
...
and (to_char(sysdate,'yyyy') - to_char(e.dateofbirth,'yyyy')) > 0
and add_months(e.dateofbirth,
(to_char(sysdate,'yyyy') - to_char(e.dateofbirth,'yyyy')) * 12)
>:fromDate:
and :toDate: > add_months(e.dateofbirth,
(to_char(sysdate,'yyyy') - to_char(e.dateofbirth,'yyyy')) * 12)
order by extract(month from e.dateofbirth) DESC,
extract(day from e.dateofbirth) DESC)