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)
(select distinct number from master.dbo.spt_values
where name is null
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
SELECT statements 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
NULL as 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)
(select number from master.dbo.spt_values
where [type] = 'P'
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
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],
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.