How to Generate a Series of Dates Between Two Given Dates in SQL Server?

Generate date range in SQL Server

In SQL Server, the task of populating a date range between two given dates in a table can be simplified using several methods. An efficient method is to use a number table or counting table to generate a series of integers.

Based on the request to populate the table with dates between '2011-09-01' and '2011-10-10', the following solution can be implemented:

<code class="language-sql">DECLARE @StartDate DATE = '20110901'
, @EndDate DATE = '20111010'

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)</code>

This code generates a list of integers from 1 to the difference between the start date and the end date. By subtracting 1 from each integer and adding it to the start date, it generates a table of dates starting with '2011-09-01' and ending with '2011-10-10'.

Alternatively, a zero-based counting table can be used to simplify calculations:

<code class="language-sql">CREATE TABLE [dbo].[nbrs](
    [nbr] [INT] NOT NULL
) ON [PRIMARY]

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

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

SELECT  DATEADD(DAY, nbr, @StartDate)
FROM    nbrs
WHERE   nbr <= DATEDIFF(DAY, @StartDate, @EndDate)</code>

By permanently storing a table of numbers in the database, you can reuse it for future queries, eliminating the need for subqueries and recursion, ensuring performance and efficiency.

The above is the detailed content of How to Generate a Series of Dates Between Two Given Dates in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!