SQL Queries to get all dates between two dates in SQL SERVER

Introduction:  

Some time we need T-SQL queries to generate dates with day names between two specific dates in SQL SERVER.

In this article I have explained using Common Table Expression (CTE), Table variable and temporary table. 

Using Common Table Expression (CTE)

There are two ways of using CTE to find the result:

Query 1:

DECLARE @StartDate AS DATE='2014-12-17';

DECLARE @EndDate AS DATE='2014-12-25';

;WITH DatesCTE AS

(

    SELECT @StartDate AS [Date],DATENAME(DW,@StartDate) AS [DayName]

    UNION ALL

    SELECT DATEADD(DAY,1,[Date]),DATENAME(DW,DATEADD(d,1,[Date])) AS [DayName]

    FROM DatesCTE

    WHERE DATE < @EndDate

)

SELECT [Date],[DayName] FROM DatesCTE OPTION (MAXRECURSION 0)

Query 2:

DECLARE @StartDate AS DATE='2014-12-17';

DECLARE @EndDate AS DATE='2014-12-25';

;WITH DatesCTE AS

(

                SELECT CAST(@StartDate AS DATETIME) AS iDate ,DATENAME(DW,@StartDate) As iDayName

                UNION ALL

                SELECT iDate + 1 ,DATENAME(Dw,iDate + 1) As iDayName

                FROM DatesCTE

                WHERE iDate + 1 <= @EndDate

)

SELECT CONVERT(VARCHAR(10),iDate,120) AS [Date] , iDayName AS[DayName]

FROM DatesCTE OPTION (MAXRECURSION 0)

Using Table Variable

DECLARE @StartDate AS DATE='2014-12-17';

DECLARE @EndDate AS DATE='2014-12-25';

DECLARE @DateTABLE (varDate DATE, varDayName VARCHAR(10))

WHILE (@StartDate<=@EndDate)

BEGIN

                INSERT @Date(varDate, varDayName) VALUES(@StartDate,DATENAME(DW,@StartDate))

                SET @StartDate=CAST(DATEADD(DAY,1,@StartDate) AS DATE)

END

SELECT varDate AS [Date], varDayName AS [DayName] FROM @Date

Using Temporary Table

DECLARE @StartDate AS DATE='2014-12-17';

DECLARE @EndDate AS DATE='2014-12-25';

CREATE TABLE #Date(TempDate DATE, TempDayName VARCHAR(10))

WHILE (@StartDate<=@EndDate)

BEGIN

                INSERT #Date(TempDate,TempDayName) VALUES(@StartDate,DATENAME(DW,@StartDate))

                SET @StartDate=CAST(DATEADD(DAY,1,@StartDate) AS DATE)

END

SELECT TempDate AS [Date], TempDayName AS [DayName] FROM #Date