Multiple SQL SERVER Queries to get all dates between two dates

Introduction:  In this article I am going to share various T-SQL queries to generate dates and their day names between two specific dates in SQL SERVER using Common Table Expression (CTE), Table variable and Temporary table. 

In previous articles i explained how to Remove duplicate records from Sql Server table and Multiple Queries to get second,third,fourth or nth highest salary  and Split large string separated by comma in Sql Server and Create,check and drop foreign key constraint on table and Find any Sql Server Table,View,Stored Procedure,User Defined Function and Schema 

Description:  While working on project I got the requirement to get all the dates between two dates along with their day names.  There are numerous ways to get the dates within the date range. I have listed few of them.

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

 Now over to you:

"A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates." 

• Tweet
• Share
• Share
• Share

Related Post