{"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":{"0":"post-1817","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-mysql","7":"category-mysql-scripts-mysql","8":"tag-all-days-mysql","9":"tag-all-sundays","10":"tag-give-all-dates-for-a-day","11":"tag-mysql","12":"tag-mysql-function","13":"tag-stored-function"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/1817","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=1817"}],"version-history":[{"count":9,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/1817\/revisions"}],"predecessor-version":[{"id":2052,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/1817\/revisions\/2052"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=1817"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=1817"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=1817"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}