Once upon a time, there was a question on SO:
We’ve got start and end dates, and we’d like to generate a list of dates in between.
How would you create a set of dates that are within a user-defined range using T-SQL?
to which I responded, back in 2009:
select dateadd(day, number, @dtBegin) from (select distinct number from master.dbo.spt_values where name is null ) n where dateadd(day, number, @dtBegin) < @dtEnd
Over the years, commentators asked that my answer be fixed, as it was not totally corrected.
So I took the time to check the SQL statement on all versions of SQL Server that I could access, 2008, 2012, 2016, and the result was always correct.
However, I also looked at the internal stored procedures, and found that the access master..spt_values with a condition WHERE type=, instead of WHERE name is NULL.
As I stated:
However, as I tried to analyze the code that MSSQL internally when querying from
spt_values, I found that the
SELECTstatements always contain the clause
WHERE [type]='[magic code]'.
Therefore I decided that although the query returns the correct result, it delivers the correct result for wrong reasons:
There may be a future version of SQL Server which defines a different
[type]value which also has
NULLas values for
[name], outside the range of 0-2047, or even non-contiguous, in which case the result would be simply wrong.
So my “official” solution now is
declare @dt datetime, @dtEnd datetime set @dt = getdate() set @dtEnd = dateadd(day, 100, @dt) select dateadd(day, number, @dt) from (select number from master.dbo.spt_values where [type] = 'P' ) n where dateadd(day, number, @dt) < @dtEnd
However, I recently came across a problem when a customer tried to deploy a T-SQL database I had developed to Azure, and reported that Azure does not support querying master..spt_values, because it does not allow cross-database queries.
So on to the internets and see what solutions people come up with.
Based on this View which generates the number of all integers in T-SQL I created a view to mimic the original spt_values table:
CREATE VIEW dbo.spt_values AS WITH Int1(z) AS (SELECT 0 UNION ALL SELECT 0) , Int2(z) AS (SELECT 0 FROM Int1 a CROSS JOIN Int1 b) , Int4(z) AS (SELECT 0 FROM Int2 a CROSS JOIN Int2 b) , Int8(z) AS (SELECT 0 FROM Int4 a CROSS JOIN Int4 b) , Int16(z) AS (SELECT TOP 2048 0 FROM Int8 a CROSS JOIN Int4 b) SELECT CAST (NULL AS NVARCHAR(35)) [name], ROW_NUMBER() OVER (ORDER BY z) -1 AS [number], CAST ('P' AS NCHAR(3)) [type], CAST (NULL AS INT) [low], CAST (NULL AS INT) [high], 0 [status] FROM Int16
The column definitions of this view are based on the definition of spt_values in the master database
CREATE TABLE [dbo].[spt_values]( [name] [nvarchar](35) NULL, [number] [int] NOT NULL, [type] [nchar](3) NOT NULL, [low] [int] NULL, [high] [int] NULL, [status] [int] NULL ) ON [PRIMARY]
During research, I often came across mentions of “14 different solutions” to enumerate dates or integers, pointing to http://www.projectdmx.com/tsql/tblnumbers.aspx, a site which is long gone. Fortunately, it is backed up in the Wayback Machine, dated 20120620.