Have been trying to write a simple SQL statement that can select a list of people from a table based on their birthdates. Thus if you are within some set days to your birthday you will be among the list selected.
I realized that, to get the query right, i had to test for some conditions, which means writing a function or a stored procedure.
I opted for the function and below are the queries written in both Microsoft SQL Server and MySql
This is the design, to check if someone’s birthday let say 31-12-1990 is within a set range say 7 ( thus it is seven or less days to his or her birthday), these are the steps taken
1. We bring the birth date year to the current year, so we get 31-12-2010.
2. We then compare if the current birth date is greater than the current date
3. If it is greater we find the difference in days between the two dates (birthdate-currentdate) and find out if the difference is between the set range
4. If current date is greater than birth date ( which means the guys birthdate is in the next year) we change the birth date year to the next year and find the difference as done in step 3. For example if the guy’s birthday is 2nd Jan and the current date is 30 Dec and the set days is 7, the guy should be in the list selected, so this condition takes care of this.
Below are the sql queries enjoy.
With this function we can then write our sql query for example
Select * from students where bdaypromt(birthday, curDate)>=0 and bdaypromt(birthday, curDate)<=@setdays