使用Oracle SQL选择具有指定范围内生日的员工 [英] Selecting employees with birthdays in given range using Oracle SQL

问题描述

对于选择两个月之间的生日，其中FROMDATE和TODATE是预准备语句中的一些参数，我想出这样的：

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?

推荐答案

最后，我们选择了垃圾不同的解决方案，我们添加fist创建周年日期：

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)

这篇关于使用Oracle SQL选择具有指定范围内生日的员工的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！