MULTIPLE SQL SERVER QUERIES TO GET ALL DATES BETWEEN TWO DATES

Implementation: Let’s write the queries to generate dates along with their names:

Using Common Table Expression (CTE)

I have mentioned two ways using CTE . You can use any of the two. Both works fine.

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 @DateList TABLE (iDate DATE,iDayName VARCHAR(10))

WHILE (@StartDate<=@EndDate)

BEGIN

                INSERT @DateList(iDate,iDayName) VALUES(@StartDate,DATENAME(DW,@StartDate))

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

END

SELECT iDate AS [Date], iDayName AS [DayName] FROM @DateList

Using Temporary Table

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

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

CREATE TABLE #DateList(iDate DATE,iDayName VARCHAR(10))

WHILE (@StartDate<=@EndDate)

BEGIN

                INSERT #DateList(iDate,iDayName) VALUES(@StartDate,DATENAME(DW,@StartDate))

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

END

SELECT iDate AS [Date], iDayName AS [DayName] FROM #DateList