BIRTHDAY PROMPT PROGRAM DESIGN

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.

IN MSSQL

   1: CREATE FUNCTION dbo.getDaysPrompt (@bdate datetime, @curDate datetime)

   2: RETURNS int AS  

   3: BEGIN 

   4:  

   5: declare @mDate as datetime

   6: declare @days as int 

   7: select @mDate=dateadd(year,datediff(year,@bdate,@curDate),@bdate)

   8: if @curDate> @mdate

   9:  select @days=datediff(day,@curDate,dateadd(year,1,@mDate))

  10: else

  11:  select @days=datediff(day,@curDate,@mDate)

  12: return @days

  13:  

  14: END

IN MYSQL

   1: DELIMITER $$

   2: DROP FUNCTION IF EXISTS `dboffman`.`bdayPrompt`$$

   3: CREATE DEFINER=`root`@`localhost` FUNCTION `bdayPrompt`(bdate date,cDate date) RETURNS int(11)

   4: BEGIN

   5:  declare mDate  date;

   6:  declare days  int default 0;

   7:  set mDate=makedate(year(cdate),dayofyear(bdate));

   8:  if cDate > mdate then

   9:  set days=datediff(date_add(mDate, interval 1 year),cDate);

  10:  else

  11:  set days=datediff(mDate,cDate);

  12:  end if;

  13:  return days;

  14:  END$$

  15: DELIMITER ;

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

Enjoy !!!!!!