Generating a range of numbers and dates in T-SQL

Act 1

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 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)
from 
    (select number from master.dbo.spt_values
     where [type] = 'P'
    ) n
where dateadd(day, number, @dt) < @dtEnd

Act 2

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]

Act 3

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.