Generate Dates between date ranges

by

Easy on SQL 2005+; easier if you have a numbers or tally table. I faked it below:

DECLARE @StartDate DATE = '20110901'
  , @EndDate DATE = '20111001'

SELECT  DATEADD(DAY, nbr - 1, @StartDate)
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS nbr
          FROM      sys.columns c
        ) nbrs
WHERE   nbr - 1 <= DATEDIFF(DAY, @StartDate, @EndDate)

If you have a tally table, replace the subquery with the table. No recursion.

EDIT: Since folks seem to have questions about the tally table, let me rewrite this using a zero-based tally table. First, here’s some code to create and populate a table.

CREATE TABLE [dbo].[nbrs](
    [nbr] [INT] NOT NULL
) ON [PRIMARY]
GO


CREATE UNIQUE CLUSTERED INDEX [clidx] ON [dbo].[nbrs]
(
    [nbr] ASC
)
GO

INSERT INTO dbo.nbrs (nbr)
SELECT nbr-1
FROM ( SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS nbr
          FROM      sys.columns c
        ) nbrs
GO

Now, that you have the numbers table as a permanent object in your database, you can reuse it for the query INSTEAD of the subquery. The query has also been edited to use a zero-based calculation.

DECLARE @StartDate DATE = '20110901'
      , @EndDate DATE = '20111001'

SELECT  DATEADD(DAY, nbr, @DateStart)
FROM    nbrs
WHERE   nbr <= DATEDIFF(DAY, @DateStart, @DateEnd)

Performant, and no recursion.

More Related Contents:

  1. I dont know sql syntax for my table [closed]
  2. CROSS JOIN vs INNER JOIN in SQL
  3. Unpivot with column name
  4. How to generate a range of numbers between two numbers?
  5. How can I get column names from a table in SQL Server?
  6. How can I select the first day of a month in SQL?
  7. SET versus SELECT when assigning variables?
  8. Multiple INSERT statements vs. single INSERT with multiple VALUES
  9. Convert varchar into datetime in SQL Server
  10. Select statement to find duplicates on certain fields
  11. How to print VARCHAR(MAX) using Print Statement?
  12. Check if a row exists, otherwise insert
  13. T-SQL datetime rounded to nearest minute and nearest hours with using functions
  14. How to update Identity Column in SQL Server?
  15. Concatenate values based on ID
  16. Splitting delimited values in a SQL column into multiple rows
  17. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  18. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  19. TransactSQL to run another TransactSQL script
  20. Store multiple bit values in a single table column
  21. Stored procedure that exports data into csv files only exports to one file
  22. Which SQL query is faster? Filter on Join criteria or Where clause?
  23. SQL Server equivalent to MySQL enum data type?
  24. How can I group by date time column without taking time into consideration
  25. TSQL – How to use GO inside of a BEGIN .. END block?
  26. Using IF ELSE statement based on Count to execute different Insert statements
  27. SQL take just the numeric values from a varchar
  28. Cumulative Total in MS Sql server [duplicate]
  29. COALESCE Function in TSQL
  30. Using CASE Statement inside IN Clause

Leave a Comment