How to Populate a Temporary Table with Dates Between Two Parameters?

Populating Temp Table with Dates Between Parameters

To populate a temporary table with dates between and including two date parameters, including the first day of the month, consider the following approach:

Step 1: Create a Recursive Common Table Expression (CTE)

;WITH cte AS (
SELECT CASE WHEN DATEPART(Day,@StartDate) = 1 THEN @StartDate 
            ELSE DATEADD(Month,DATEDIFF(Month,0,@StartDate)+1,0) END AS myDate
UNION ALL
SELECT DATEADD(Month,1,myDate)
FROM cte
WHERE DATEADD(Month,1,myDate) <=  @EndDate
)

Step 2: Select Dates from CTE

SELECT myDate
FROM cte
OPTION (MAXRECURSION 0)

Example:

For @StartDate = '2011-01-01' and @EndDate = '2011-08-01', the following dates will be returned in the temporary table:

2011-01-01
2011-02-01
2011-03-01
2011-04-01
2011-05-01
2011-06-01
2011-07-01
2011-08-01

Note:

This approach handles cases where @StartDate is not the first day of the month by starting the recursion with the first of the next month. If you want to start with @StartDate regardless, remove the 1 from the inner SELECT statement in the CTE definition.

The above is the detailed content of How to Populate a Temporary Table with Dates Between Two Parameters?. For more information, please follow other related articles on the PHP Chinese website!