SQL Query to return a table containing all dates falling within a date range along with their week day names

Following is the T-SQL code to return all dates falling within a given date range. This uses CTE for populating all dates, and store them in a temporary table.

DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = '01-Apr-2013'
SET @EndDate = '30-Apr-2013'

;WITH DatesList
AS
(
SELECT @StartDate [fldDate]
UNION ALL
SELECT fldDate + 1 FROM DatesList WHERE fldDate < @EndDate
)

SELECT fldDate, DATENAME(dw, fldDate) as fldDayFullName INTO #DatesList FROM DatesList option (maxrecursion 0)

SELECT * FROM #DatesList

CodeProject