How to Efficiently Retrieve a Date Range in SQL Server?

Retrieve all dates between two specific dates in SQL Server

Requirements

The goal is to get a complete list of all dates in a specified range from start date to end date. This information will be stored in the cursor for subsequent processing.

Challenge

To accomplish this task, a query using a recursive Common Table Expression (CTE) is implemented:

<code class="language-sql">;with GetDates As (
    select DATEADD(day,1,@maxDate) as TheDate
    UNION ALL
    select DATEADD(day,1, TheDate) from GetDates
    where TheDate < @minDate
)
SELECT TheDate FROM GetDates</code>

However, an error occurred while trying to populate the cursor with the CTE results:

<code class="language-sql">SET @DateCurSor = CURSOR FOR
SELECT TheDate
FROM GetDates</code>

The error message states a syntax error near the keyword "SET".

Solution

Another method is recommended, which is to use a calendar. If the calendar table does not exist, it can be easily created. This table provides a tabular representation of dates, greatly simplifying the retrieval process.

<code class="language-sql">DECLARE @MinDate DATE = '20140101',
        @MaxDate DATE = '20140106';

SELECT Date
FROM dbo.Calendar
WHERE Date >= @MinDate
AND Date <= @MaxDate;</code>

If there is no calendar table, a simple procedure involving the system table sys.all_objects is provided:

<code class="language-sql">DECLARE @MinDate DATE = '20140101',
        @MaxDate DATE = '20140106';

SELECT TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
        Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b;</code>

Avoid using cursors

Using cursors in SQL Server is generally not recommended. In contrast, set-based operations are more popular due to their superior performance. In the scenario described, the task of determining the quantity of a specific item on a specific date can be achieved without using cursors:

<code class="language-sql">SELECT TOP 1 date, it_cd, qty 
FROM T
WHERE it_cd = 'i-1'
AND Date BETWEEN @MinDate AND @MaxDate;</code>

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