Fun with Dates

Don’t get your hopes up, this has nothing to with date nights !!   😉  Now we know how to find number of days between two dates, add days and so on.  Few days ago I needed to list out all the valid dates between two given dates.  Here is a quick way to do that :

with temp1(mydate) as
(
values date(‘2011-01-01’)
union all
select date(mydate) + 1 days
from temp1
where date (mydate) < date (‘2011-12-31’)
)
select date(mydate)
from temp1
;
This will list all dates between 2011-01-01 and 2011-12-31.

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 :

with temp1(mydate) as
(
values date(‘2011-01-01’)
union all
select date(mydate) + 1 days
from temp1
where date (mydate) < date (‘2011-12-31’)
)

select WEEK(date(mydate))as week_no,
date(mydate) – (dayofweek(date(mydate)) – 1) days as first_day_of_week,
date(mydate) + (7 – dayofweek(date(mydate))) days as last_day_of_week
from temp1
group by WEEK(date(mydate))
,date(mydate) – (dayofweek(date(mydate)) – 1) days
,date(mydate) + (7 – dayofweek(date(mydate))) days
;

You can build numerous iterations from this. Happy SQL’ng !   Comment better ways to do the same.