Please disable your adblock and script blockers to view this page
 

Multiple SQL SERVER Queries to get all dates between two dates

Multiple SQL SERVER Queries to get all dates between two datesIntroduction 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. 


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." 
Previous
Next Post »

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily.. ConversionConversion EmoticonEmoticon