How to get the day of week of a all dates between start and end date in T-SQL

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