Display all the date from start interval to end interval using Recursive CTE

Suppose we have a given starting date and end date and we need to list all the dates and weekday in that interval. Below is how this can be accomplished

declare @a as table(a date)

declare @b as table(b date)

insert @a values('2011-4-22')

insert @b values('2011-4-30')

;with e as(

select a as dayt, DATENAME(WEEKDAY,a) as weekday, 0 as lvl from @a aa

union all

select DATEADD(DAY,1,aa.dayt) as dayt,DATENAME(WEEKDAY,DATEADD(DAY,1,aa.dayt)) as weekday, lvl+1

from @b bb inner join e aa ON aa.dayt<bb.b


select dayt as date,weekday from e


Tags: ,

SQL Server | SQL Tricks | Recursive CTE