How to Generate Date Ranges in SQL Server?

Generating Date Ranges within SQL Server

Frequently, database administrators need to populate tables with date ranges. This article demonstrates efficient SQL Server methods to achieve this.

One common technique uses a ROW_NUMBER() subquery to generate a numerical sequence:

<code class="language-sql">DECLARE @StartDate DATE = '20110901';
DECLARE @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);</code>

This subquery creates a series of numbers, then DATEADD() increments the start date by each number to produce the date range.

A more performant alternative utilizes a pre-existing tally table (if available):

<code class="language-sql">SELECT DATEADD(DAY, nbr, @StartDate)
FROM nbrs
WHERE nbr <= DATEDIFF(DAY, @StartDate, @EndDate);</code>

This avoids recursive queries, offering a significant speed advantage when dealing with large date ranges. The tally table contains a pre-generated sequence of numbers, eliminating the need for on-the-fly number generation.

The above is the detailed content of How to Generate Date Ranges in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!