13 Sep

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 ;

2 thoughts on “MySQL function returns dates of specified day & between date range

Leave a Reply

-- Kedar Vaijanapurkar --