{"id":1817,"date":"2012-09-13T07:49:24","date_gmt":"2012-09-13T07:49:24","guid":{"rendered":"http:\/\/kedar.nitty-witty.com\/?p=1817"},"modified":"2015-02-26T14:07:39","modified_gmt":"2015-02-26T14:07:39","slug":"mysql-function-to-get-dates-specified-day-date-range","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/mysql-function-to-get-dates-specified-day-date-range","title":{"rendered":"MySQL function returns dates of specified day & between date range"},"content":{"rendered":"

This mysql stored function will return you all the dates between given dates having specified day.
\nIn short if you say: give me all Sundays in this month.<\/p>\n

I have following for you:<\/p>\n

mysql> select Give_Me_Dates_Days(1,'2012-09-01','2012-10-01') as All_Sundays;
\n+--------------------------------------------------------+
\n| All_Sundays |
\n+--------------------------------------------------------+
\n| 2012-09-02,2012-09-09,2012-09-16,2012-09-23,2012-09-30 |
\n+--------------------------------------------------------+<\/code><\/p>\n

The function parameters are as follows: Give_Me_Dates_Days(DAY_IDENTIFIER,START_DATE,END_DATE)<\/p>\n

Sunday = 1 (and rest you can figure-out).
\n
\n[ Ofcourse I’ve not done much validations eg. start_date < end_date. ]\n\n\nCheck 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<\/b><\/a><\/p>\n

\ufeffDELIMITER $$
\nDROP function IF EXISTS `Give_Me_Dates_Days` $$
\nCREATE function `Give_Me_Dates_Days` (in_day int, in_date1 timestamp, in_date2 timestamp) returns varchar(4000) deterministic
\nBEGIN
\n DECLARE tot_dates int;
\n DECLARE proc_date timestamp;
\n DECLARE dates varchar(4000) default '';
\n #1= sunday
\n# drop temporary table if exists selecteddates;
\n# create temporary table selecteddates ( dates timestamp );
\n set proc_date=in_date1;
\n while proc_date < in_date2\n do\n if (dayofweek(proc_date)=in_day) then\n set dates=concat(dates,date(proc_date),',');\n# insert into selecteddates values (proc_date);\n end if;\n set proc_date=date_add(proc_date, interval 1 day);\n end while;\n return trim(trailing ',' from dates);\nEND $$\nDELIMITER ;<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"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…\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,378],"tags":[342,340,339,427,341,343],"class_list":["post-1817","post","type-post","status-publish","format-standard","category-mysql","category-mysql-scripts-mysql","tag-all-days-mysql","tag-all-sundays","tag-give-all-dates-for-a-day","tag-mysql","tag-mysql-function","tag-stored-function"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t