This tutorial demonstrates 2 ways for you how to obtain the date range in SQL Server based on between date start and end date parameters. One is a algorithm oriented way and the other is a SQL’s function oriented way.
Below is a specified example :
The input :
+ Start date : ‘2012-03-20’
+ End date : ‘2012-03-30’
The output :
Expected list of dates
1. The first way : Use simple algorithm loop through from start date until end date as other languagues
CREATE FUNCTION dbo.fGetListOfDates ( @startDate DATETIME, -- start date @endDate DATETIME -- end date ) RETURNS @retDates TABLE ( [DateAndTime] DATETIME, -- date time NameOfDay VARCHAR(10) -- name of day ) AS BEGIN DECLARE @tempDate DATETIME SET @tempDate = @startDate WHILE CONVERT(VARCHAR(10), @tempDate, 120) <= -- Compare date without time CONVERT(VARCHAR(10), @endDate, 120) BEGIN INSERT INTO @retDates VALUES ( @tempDate, DATENAME(dw, @tempDate) ) SET @tempDate = DATEADD(D, 1, @tempDate) -- Get next day END RETURN END |
2. The second way : Use “Recursive Queries Using Common Table Expressions”
CTE – Common Table Expression is one of the new features in SQL SERVER 2005. It can be used instead of temp table or table variable in the stored procedure.
CREATE FUNCTION dbo.fGetListOfDates ( @startDate DATETIME, -- start date @endDate DATETIME -- end date ) RETURNS @retDates TABLE ( [DATE] DATETIME, -- date time NameOfDay VARCHAR(10) -- name of day ) AS BEGIN WITH tblDates([DATE]) AS ( SELECT @startDate AS [DATE] UNION ALL SELECT DATEADD(d,1,[DATE]) FROM tblDates WHERE [DATE] < @endDate ) INSERT INTO @retDates SELECT [DATE], DATENAME(dw,[DATE]) AS NameOfDay FROM tblDates RETURN END |
3. After create this function,then run this below statement, we will get expected result as the above image
SELECT * FROM dbo.fGetListOfDates('2012-03-20','2012-03-30') |
Hope it helpful for you :)!
If you have any way, please let me know by leaving your comment.!
