Don’t get your hopes up, this has nothing to with date nights !! \u00c2\u00a0 \ud83d\ude09 \u00c2\u00a0Now we know how to find number of days between two dates, add days and so on. \u00c2\u00a0Few days ago I needed to list out all the valid dates between two given dates. \u00c2\u00a0Here is a quick way to do that :<\/p>\n
with temp1(mydate) as<\/span> Just to add little more fun to this, what if you need to list out all the week number, first day of the week and last day of the week for the same date range :<\/p>\n with temp1(mydate) as<\/span> select WEEK(date(mydate))as week_no,<\/span> You can build numerous iterations from this. Happy SQL’ng ! \u00c2\u00a0 Comment better ways to do the same.<\/p>\n","protected":false},"excerpt":{"rendered":" Don’t get your hopes up, this has nothing to with date nights !! \u00c2\u00a0 \ud83d\ude09 \u00c2\u00a0Now we know how to find number of days between two dates, add days and so on. \u00c2\u00a0Few days ago I needed to list out all the valid dates between two given dates. \u00c2\u00a0Here is a quick way to do […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-container-style":"default","site-container-layout":"default","site-sidebar-layout":"default","site-transparent-header":"default","disable-article-header":"default","disable-site-header":"default","disable-site-footer":"default","disable-content-area-spacing":"default","footnotes":""},"categories":[3,358],"tags":[943,629,636,632,637,635,631,633,634,630],"class_list":["post-1094","post","type-post","status-publish","format-standard","hentry","category-db2","category-how-to","tag-db2","tag-db2-date-functions","tag-db2-fun-with-dates","tag-first-day-of-the-week","tag-fun-with-date","tag-get-all-valid-date-between-two-dates","tag-group-by-weeks","tag-last-day-of-the-week","tag-list-all-dates","tag-list-all-days"],"_links":{"self":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1094","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1094"}],"version-history":[{"count":5,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1094\/revisions"}],"predecessor-version":[{"id":1502,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1094\/revisions\/1502"}],"wp:attachment":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1094"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1094"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1094"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n(<\/span>
\nvalues date(‘2011-01-01’)<\/span>
\nunion all<\/span>
\nselect date(mydate) + 1 days<\/span>
\nfrom temp1<\/span>
\nwhere date (mydate) < date (‘2011-12-31’)<\/span>
\n)<\/span>
\nselect date(mydate)<\/span>
\nfrom temp1<\/span>
\n; <\/span>
\nThis will list all dates between 2011-01-01 and 2011-12-31.<\/p>\n
\n(<\/span>
\nvalues date(‘2011-01-01’)<\/span>
\nunion all<\/span>
\nselect date(mydate) + 1 days<\/span>
\nfrom temp1<\/span>
\nwhere date (mydate) < date (‘2011-12-31’)<\/span>
\n)<\/span><\/p>\n
\ndate(mydate) – (dayofweek(date(mydate)) – 1) days as first_day_of_week,<\/span>
\ndate(mydate) + (7 – dayofweek(date(mydate))) days as last_day_of_week<\/span>
\nfrom temp1<\/span>
\ngroup by WEEK(date(mydate))<\/span>
\n,date(mydate) – (dayofweek(date(mydate)) – 1) days<\/span>
\n,date(mydate) + (7 – dayofweek(date(mydate))) days<\/span>
\n;<\/span><\/p>\n