Here is a quick CTE that can be used to generate all the dates in a range.
DECLARE @startDate DATE = GETDATE() - 30;
DECLARE @endDate DATE = GETDATE();
; WITH datesCTE as
(
SELECT @startDate as theDate
UNION ALL
SELECT dateadd(day, 1, theDate) as theDate
FROM datesCTE
WHERE theDate <= @endDate
)
SELECT *
FROM datesCTE
OPTION (MAXRECURSION 1000);
This is handy to use when trying to find dates missing from a set, you can outer join to it, or union with it to fill in the blanks.
Download my book for FREE when you sign up for my mailing list.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
I disagree with this approach to programming in SQL. Since this is a declarative database language, it would be better to build a calendar table that includes columns for ordinal dates. That is the earliest date and our table begins with a straight ordinal number of one and is incremented for every date to the last date for storing. A second column would have an ordinal number of business days. The third column could be whatever you want. The important point is that I can look up two dates, and subtract their ordinal positions in my calendar to get the range in between, without having to do procedural code.
Thanks for the article. I appreciate anyone that takes the time to share knowledge.
My recommendation would be to avoid rCTEs (recursive CTEs) that produce a sequence (basically, they count incrementally from one value to another) like the plague. They’re slower than a well written while loop and use 8 time more logical reads. They use a surprisingly large amount of resources even for such low row counts. And, they’re wicked easy to avoid in favor of a much faster, easier on resources, and easier to write code for method using cCTEs (cascading CTEs) originally defined by Itzek Ben-Gan many years ago. The following link provides my rendition for that mehod in the form of a very high performance, low resource usage, easy to use iTVF (inline Table Valued Function).
https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
As for proof about why I say to avoid rCTEs that make an incrementatal count of any type, please see the following link for actual performance tests that compare 3 other much better methods againt rCTEs…
https://www.sqlservercentral.com/articles/hidden-rbar-counting-with-recursive-ctes
Thanks Jeff, I appreciate your feedback and examples.
-Steve Stedman
And sorry about how all that is jammed up in a single paragraph. Apparently the posting software doesn’t understand what the [Enter] key is supposed to render out as.
Almost forgot. Here’s the code comparison… I double spaced it to see how it would render out based on the previous lesson learned about the posting code for this site.
–===== The parameters from the original code.
DECLARE @startDate DATE = GETDATE() – 30
,@endDate DATE = GETDATE()
;
SET STATISTICS TIME,IO ON;
–===== The original code with the rCTE
— This method also includes 1 unwanted extra day.
WITH datesCTE as
(
SELECT @startDate as theDate
UNION ALL
SELECT dateadd(day, 1, theDate) as theDate
FROM datesCTE
WHERE theDate <= @endDate –this causes the extra day
)
SELECT *
FROM datesCTE
OPTION (MAXRECURSION 1000)
;
–===== Print an unmeasured separator in the output
SET STATISTICS TIME,IO OFF;
PRINT REPLICATE('=',119);
SET STATISTICS TIME,IO ON
;
–===== The method that uses a Tally function and
— doesn't produce the extra day.
— Notice how short and easy it is.
SELECT theDate = DATEADD(dd,t.N,@startDate)
FROM dbo.fnTally(0,DATEDIFF(dd,@startDate,@endDate)) t
;
SET STATISTICS TIME,IO OFF;
GO
Also notice the statistic output. 194 page reads (= 1.5MB of IO) for the original code. None for the function code.
(32 rows affected)
Table 'Worktable'. Scan count 2, logical reads 194, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.
=======================================================================================================================
(31 rows affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Like I said, seriously consider NOT using rCTEs even for such low row counts.
The comments are again an expression that everything should be made as it once was.
How many tables need to be made? One on an hourly basis, one on a weekly basis, one on a monthly basis, … In Denmark we have Tivoli, where a day is calculated from 2 o’clock at night to 2 o’clock the next day. How far should all these tables go back in time and how far should they go forward in time? How should they and by whom are they maintained? Should all programs who use these tables take into account the lack of data in these tables – YES! Correctness is more important than using old-fashioned principles! Fortunately, languages are evolving so that new and safer methods can be used. We do not code in assemply and Cobol anymore. Cobol only to maintain the kilometers long programs that have been developed over time and not in a snap change to more modern languages with new and more safe functionalities. And even SQL has changed over time!!!!!
Tally performs worse if your tally function is not already compiled and optimized. However, as I have shown before, the compilation time of the tally function significantly exceeds the time of the recursive version. Since the recursive one finishes compiling and executing long before the tally version finishes compiling, the time difference means absolutely nothing. What does 1.5 MB IO on cached data on a server that we use on the doorstep to the year 2022. But this insignificant overbearing that IO operations obviously exceeds the marked savings on CPU resources. CPU resources are still to be licensed not IO-operations. But of course it is more important to point out an insignificant error in the code that any programmer just corrects! If it is too difficult – correct <= to <. All programmers tests the code before use in production. And this error would be found. But using the tally-function and then more resources, will not be found.
Like I said, seriously consider using rCTEs for such low row counts. The performance is better than using tally and less test for correct code than using tables.