How can I list all dates between two date parameters in SQL Server, without creating a stored procedure, calendar table or recursive function?
3
-
Can you explain why you'd like to avoid SPs, calendar tables and recursive CTEs? That might help us understand what you are trying to achieve and limitations that stand in your way.David Rushton– David Rushton2015-12-18 11:03:45 +00:00Commented Dec 18, 2015 at 11:03
-
When you only have permission to query a database, it is not possible to add in functions or other tables.SQLSam– SQLSam2015-12-18 11:32:26 +00:00Commented Dec 18, 2015 at 11:32
-
Understood. @John Bell's answer below will would for you. Recursive CTEs create result sets that exist only at runtime. Read only permissions won't be a problem here.David Rushton– David Rushton2015-12-18 11:53:48 +00:00Commented Dec 18, 2015 at 11:53
Add a comment
|
4 Answers
There's always the recursive CTE option:
DECLARE @STARTDATE DATETIME
DECLARE @ENDDATE DATETIME
SET @STARTDATE = '2015-01-01'
SET @ENDDATE = '2015-12-31'
;WITH DATE_RANGE (DATES) AS (
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @STARTDATE), 0)
UNION ALL SELECT DATEADD(DAY, 1, DATES)
FROM DATE_RANGE
WHERE DATEADD(DAY, 1, DATES) <= @ENDDATE)
SELECT DATES
FROM DATE_RANGE
OPTION (MAXRECURSION 0)
Be sure to use the MAXRECURSION option, or your results will be limited to 100 as default.
Comments
This uses Row_Number on the spt_values table in Master database to create a list of years, months and dates within the date range.
This is then built into a datetime field, and filtered to only return dates within the date parameters entered.
Very quick to execute and returns 500 years worth of dates (182987 days) in less than 1 second.
Declare @DateFrom datetime = '2000-01-01'
declare @DateTo datetime = '2500-12-31'
Select
*
From
(Select
CAST(CAST(years.Year AS varchar) + '-' + CAST(Months.Month AS varchar) + '-' + CAST(Days.Day AS varchar) AS DATETIME) as Date
From
(select row_number() over(order by number) as Year from master.dbo.spt_values) as Years
join (select row_number() over(order by number) as Month from master.dbo.spt_values) as Months on 1 = 1
join (select row_number() over(order by number) as Day from master.dbo.spt_values) as Days on 1 = 1
Where
Years.Year between datepart(year,@DateFrom) and datepart(year,@DateTo)
and Months.Month between 1 and 12
and
Days.Day between 1 and datepart(day,dateadd(day,-1,dateadd(month,1,(CAST(CAST(Years.Year AS varchar)+'-' + CAST(Months.Month AS varchar) + '-01' AS DATETIME)))))
) as Dates
Where Dates.Date between @DateFrom and @DateTo
order by 1
Comments
Following will be a solution for YOU
DECLARE @DATE1 DATE
DECLARE @DATE2 DATE
SET @DATE1 ='20020101'
SET @DATE2 = '20020311'
SELECT @DATE1 as t
into #FromDate
DECLARE cur CURSOR FOR
SELECT t FROM #FromDate
OPEN cur
FETCH NEXT FROM cur INTO @DATE1
WHILE(@@FETCH_STATUS=0)
BEGIN
IF(@DATE1<=@DATE2)
INSERT INTO #FromDate
VALUES(DATEADD(DAY,1,@DATE1))
FETCH NEXT FROM cur INTO @DATE1
END
CLOSE cur
DEALLOCATE cur;
SELECT t FROM #FromDate;
DROP TABLE #FromDate;
Comments
Simple result.
DECLARE @DATE1 DATE
DECLARE @DATE2 DATE
SET @DATE1 ='20020101'
SET @DATE2 = '20020311'
WHILE(@DATE1<=@DATE2)
Begin
PRINT @DATE1
set @DATE1 = DATEADD(dd,1,@DATE1)
END