Generate a resultset of incrementing dates in TSQL

by

If your dates are no more than 2047 days apart:

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

I updated my answer after several requests to do so. Why?

The original answer contained the subquery

 select distinct number from master.dbo.spt_values
     where name is null

which delivers the same result, as I tested them on SQL Server 2008, 2012, and 2016.

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.

More Related Contents:

  1. Is it possible to write single query on two table which are not connected to each other?
  2. How can I do an UPDATE statement with JOIN in SQL Server?
  3. SQL update query using joins
  4. How to update two tables in one statement in SQL Server 2005?
  5. SQL query to select dates between two dates
  6. Create a date from day month and year with T-SQL
  7. Selecting COUNT(*) with DISTINCT
  8. Querying data by joining two tables in two database on different servers
  9. How to return multiple values in one column (T-SQL)?
  10. Getting result of dynamic SQL into a variable for sql-server
  11. How to get difference between two rows for a column field?
  12. SQL – Subtracting a depleting value from rows
  13. What’s the best way to select the minimum value from several columns?
  14. What is the difference between using a cross join and putting a comma between the two tables?
  15. SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints
  16. Correct use of transactions in SQL Server
  17. SELECT FOR UPDATE with SQL Server
  18. Why do you create a View in a database?
  19. How to split string using delimiter char using T-SQL?
  20. OPENXML with xmlns:dt
  21. How to check which locks are held on a table
  22. SQL server select distinct rows using most recent value only
  23. Eliminate and reduce overlapping date ranges
  24. SQL Server search using like while ignoring blank spaces
  25. Use variable with TOP in select statement in SQL Server without making it dynamic [duplicate]
  26. Compare dates in T-SQL, ignoring the time part
  27. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  28. Is there a quick way to check if ANY column is NULL?
  29. Why do SQL Server Scalar-valued functions get slower?
  30. SQL query for parent-child chain

Leave a Comment