Choosing employees with birthdays in a given range using Oracle SQL

Question

To select birthdays between two months, where FROMDATE and TODATE are some parameters in the prepared statement, I realized 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 for working with the days?

Answer 1

In the end, we chose another solution, in which we add a fist, create an 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) 

Answer 2

There are several ways to find date ranges in Oracle. For your scenario, I suggest you include the month and day elements of all dates associated with the 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 will not use the index in the e.dateOfBirth column. It doesn't matter if it depends on how often you want to run the query.

---

Comments by @AdeelAnsari:

"I don't like doing a predicate to use an index"

Which index? The normal index on dateOfBirth will not be useful because the index entries will contain the element of the year. So this will not help you find all the records about people born on December 23.

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

Answer 3

you should be able to use

 SELECT * FROM mytbale where dateofbirth between start_dt and end_dt 

alternate:

You can convert dates to day of the year using:

 to_char( thedate, 'ddd' ) 

then check the range (note that this has the same problem as @Dems, where you shouldn't cover the end of the year as from December 25th to January 10th.)

Answer 4

Do you need maximum performance and are therefore ready to make changes to the circuit? Or is the number of records so small, and performance is relatively unimportant, what do you want a query that will work with data as is?

The easiest and fastest way to do this is to save the second field of birth data, but without the year. I put quotes around "without" because the date cannot actually be a year. So, instead, you just base it on another year.

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

 WHERE DoB2000 >= FromDate AND DoB2000 <= ToDate 

(This assumes that you are also indexing the new field to speed up the search, otherwise you will still get a scan, although it MAY be faster than the next alternative anyway.)

Alternatively, you can continue to use the EXTRACT template. But this will have a sad consequence; it is very dirty and you will never get an Index Seek, you will always get an index scanner. This is because the desired 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) ) ) 

Answer 5

This is the request that I use for birtdates over 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 the format 38.9, 27.2, etc.

• Using ceil() will give us the difference in the years that we need to determine if this person is close to the date of birth. For instance.

  • Age: 29.9
  • 29.9 + (20 days) = 30.2
  • ceil (30.1) - ceil (29.9) = 1

This is the result of the query in 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 

Answer 6

I don't know how this works in terms of performance, but I would try using length subtraction. Say, for example, you want birthdays to be between January 1 and July 1. Anyone between 25 and 25.5 will qualify. That is, anyone whose incomplete age is less than 1/2 year will qualify. The math is simple in 1/2, but it is the same regardless of the window. In other words, “within one month” means +/- 1/12 of the year, within 1 day means +/- 1/365 of the year, etc.

The year does not matter in this method, so I will use the current year when creating the variable.

In addition, I would think about the center of the range, and then make absolute values ​​(either that or always use the future date).

for instance

 select personid from mytable where abs(mod(dob - target_birth_date)) < 1/52 

will give you all a happy birthday for a week.

I understand that this code is hardly pseudo-code, but it looks like it can do it, and you can still use indexes if you modify it a little. Just trying to think outside the box.

Answer 7

What a solution !!!

 SELECT * FROM PROFILE prof where to_date(to_char(prof.BIRTHDATE, 'DDMM'), 'DDMM') BETWEEN sysdate AND sysdate+5 

or

 add_months(to_date(to_char(prof.BIRTHDATE, 'DDMM'), 'DDMM'),12) BETWEEN sysdate AND sysdate+5 

Answer 8

Guys I have a simpler solution to this problem

• step 1. convert the month to a number,
• Step 2. Connect the day (in two digits) with the month
• step 3. then convert the result to a number by doing to_number (result)
• Step 4. Once you have it for the start and end dates, then perform a function between them and and execute.

code example:

 SELECT date_of_birth FROM xxxtable where to_number(to_number(to_char(to_date(substr(date_of_birth,4,3),'mon'),'mm'))||substr(date_of_birth,1,2)) between to_number(to_number(to_char(to_date(substr(:start_date_variable,4,3),'mon'),'mm'))||(substr(:start_date_variable,1,2))) and to_number(to_number(to_char(to_date(substr(:end_date_variable,4,3),'mon'),'mm'))||(substr(:end_date_variable,1,2)));