MySQL function returns dates of specified day & between date range

This mysql stored function will return you all the dates between given dates having specified day.In short if you say: give me all Sundays in this month.

I have following for you:

mysql> select Give_Me_Dates_Days(1,'2012-09-01','2012-10-01') as All_Sundays;
+--------------------------------------------------------+
| All_Sundays |
+--------------------------------------------------------+
| 2012-09-02,2012-09-09,2012-09-16,2012-09-23,2012-09-30 |
+--------------------------------------------------------+

The function parameters are as follows: Give_Me_Dates_Days(DAY_IDENTIFIER,START_DATE,END_DATE)

Sunday = 1 (and rest you can figure-out).

[ Ofcourse I’ve not done much validations eg. start_date < end_date. ] Check the code for getting dates of specified days between a date-range as follows. You may download the sql here:Give_Me_Dates_Days.sql

DELIMITER $$
DROP function IF EXISTS `Give_Me_Dates_Days` $$
CREATE function `Give_Me_Dates_Days` (in_day int, in_date1 timestamp, in_date2 timestamp) returns varchar(4000) deterministic
BEGIN
DECLARE tot_dates int;
DECLARE proc_date timestamp;
DECLARE dates varchar(4000) default '';
#1= sunday
# drop temporary table if exists selecteddates;
# create temporary table selecteddates ( dates timestamp );
set proc_date=in_date1;
while proc_date < in_date2 do if (dayofweek(proc_date)=in_day) then set dates=concat(dates,date(proc_date),','); # insert into selecteddates values (proc_date); end if; set proc_date=date_add(proc_date, interval 1 day); end while; return trim(trailing ',' from dates); END $$ DELIMITER ;