Selecting employees with birthdays in given range using Oracle SQL

Question

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?

Solution 1

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)

Solution 2

There's more than one way to search date ranges in Oracle. For your scenario I suggest you turn the month and day elements of all the dates involved into numbers.

select
  p.id as person_id,
   ...
   ...
  where e.active = 1
  and to_number (to_char( e.dateOfBirth, 'MMDD')) 
      between to_number (to_char( FROMDATE, 'MMDD'))
              and to_number (to_char( TODATE, 'MMDD')) 
  order by extract(month from e.dateOfBirth) DESC,
          extract(day from e.dateOfBirth) DESC

This won't use any index on the e.dateOfBirth column. Whether that matters depends on how often you want to run the query.

---

@AdeelAnsari comments:

" I don't like to to_char the predicate, in order to make use of index"

What index? A normal index on dateOfBirth isn't going to be of any use, because the index entries will lead with the year element. So it won't help you find all the records of people born on any 23rd December.

A function-based index - or in 11g, a virtual column with an index (basically the same thing) - is the only way of indexing parts of a date column.

Solution 3

you should be able to use

SELECT * FROM mytbale
where dateofbirth between start_dt and end_dt

alternate:

you can convert dates to the day of the year using:

to_char( thedate, 'ddd' )

then check the range (note this has the same issue as @Dems answer where you should not span the end of the year as in Dec 25th through Jan 10th.)

Solution 4

Do you need maximum performance, and so are willing to make a change to the schema? Or are the number of records so small, and performance relatively un-important, that you want a query that will work with the data as-is?

The simplest and fastest way to do this is to store a second data-of-birth field, but 'without' the year. I put quotes around 'without' because a date can't actually not have a year. So, instead, you just base it on another year.

Re-dating every DoB to the year 2000 is a good choice in my experience. Because it includes a leap-year and is a nice round number. Every DoB and FromDate and ToDate will work in the year 2000...

WHERE
      DoB2000 >= FromDate
  AND DoB2000 <= ToDate

(This assumes you also index the new field to make the search quick, otherwise you still get a scan, though it MAY be faster than the following alternative anyway.)

Alternatively, you can keep using the EXTRACT pattern. But that will have an unfortunate consequence; it's extremely messy and you will never get an Index Seek, you will always get an Index Scan. This is because of the fact that the searched field is wrapped in a function call.

WHERE
  (   EXTRACT(month FROM e.DateOfBirth) > EXTRACT(month FROM FromDate)
      OR  (      EXTRACT(month FROM e.DateOfBirth)  = EXTRACT(month FROM FromDate)
             AND EXTRACT(day   FROM e.DateOfBirth) >= EXTRACT(day   FROM FromDate)
          )
  )
  AND
  (   EXTRACT(month FROM e.DateOfBirth) < EXTRACT(month FROM ToDate)
      OR  (      EXTRACT(month FROM e.DateOfBirth)  = EXTRACT(month FROM ToDate)
             AND EXTRACT(day   FROM e.DateOfBirth) <= EXTRACT(day   FROM ToDate)
          )
  )

Solution 5

This is the query that I am using for birtdates in the next 20 days:

SELECT A.BIRTHDATE, 
    CEIL(ABS (MONTHS_BETWEEN(A.BIRTHDATE, SYSDATE) / 12)) AGE_NOW,  
    CEIL(ABS (MONTHS_BETWEEN(A.BIRTHDATE, SYSDATE + 20) / 12)) AGE_IN_20_DAYS
FROM USERS A
WHERE 
    CEIL(ABS (MONTHS_BETWEEN(A.BIRTHDATE, SYSDATE) / 12)) <> CEIL(ABS (MONTHS_BETWEEN(A.BIRTHDATE, SYSDATE + 20) / 12));

• ABS (MONTHS_BETWEEN(A.BIRTHDATE, SYSDATE) / 12)) return the age in format 38.9, 27.2, etc

• Applying ceil() will give us the difference in years that we need to determinate if this person is near to have a birthdate. Eg.

  1. Age: 29.9
  2. 29.9 + (20 days) = 30.2
  3. ceil(30.1) - ceil(29.9) = 1

This is the result of querying at december 16:

BIRTHDATE    AGE_NOW    AGE_IN_20_DAYS

12/29/1981    35           36
12/29/1967    49           50
1/3/1973      44           45
1/4/1968      49           50

View more solutions