If you want to get a list of all dates between a start and end date and their corresponding day of week in T-SQL, you can use the following code:
set datefirst 1 declare @StartDate date = '2014-01-20' declare @EndDate date = '2014-01-27' ;with cte (dt, dw) as ( select cast(@StartDate as date), datepart(dw, @StartDate) union all select dateadd(day, 1, dt), datepart(dw, dateadd(day, 1, dt)) from cte where dateadd(day, 1, dt) <= @EndDate ) select dt, dw from cte
Result