Link to home
Start Free TrialLog in
Avatar of Vasanth_Kumar
Vasanth_Kumar

asked on

Employee DoB details between two given dates

query requied on  Employee dob details between to given dates, but year not requied. like ( 01 -05) between (31-05)
ASKER CERTIFIED SOLUTION
Avatar of GGuzdziol
GGuzdziol
Flag of Luxembourg image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JacekMycha
JacekMycha
SELECT *  FROM employee
WHERE dob BETWEEN TO_NUMBER(TO_CHAR(:start_date, 'DDD')) AND TO_NUMBER(TO_CHAR(:end_date, 'DDD'));

'DDD' is format mask for 'Day of Year (1-366)'.
TO_CHAR(:start_date, 'DDD') returns 'Day of Year' as a string, so '2'>'111'. Therefore you have to convert TO_NUMBER.

I don't know how to handle Feb 29th, e.g. TO_NUMBER(TO_CHAR('29-FEB-04', 'DDD')) = 60, but also TO_NUMBER(TO_CHAR('01-MAR-05', 'DDD')) = 60. It's not a problem if both dates are the same year.

JacekMycha