使用介于 2 个日期参数之间(包括 2 个日期参数)的日期填充临时表的最简单方法 [英] Easiest way to populate a temp table with dates between and including 2 date parameters

问题描述

用包含和介于 2 个日期参数之间的日期填充临时表的最简单方法是什么.我只需要一个月的第一天日期.

What is the easiest way to populate a temp table with dates including and between 2 date parameters. I only need the 1st day of the month dates.

例如如果@StartDate = '2011-01-01' 和@EndDate = '2011-08-01'

So for example if @StartDate = '2011-01-01' and @EndDate = '2011-08-01'

然后我希望在表中返回这个

Then I want this returned in the 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

推荐答案

即使 @StartDate 不是本月的第一天，这也有效.我假设如果不是月初，您希望从下个月的第一天开始.否则删除 +1.:

This works even if the @StartDate is not the first of the month. I'm assuming that if it's not the start of the month, you want to begin with the first of the next month. Otherwise remove the +1.:

;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
)
SELECT myDate
FROM cte
OPTION (MAXRECURSION 0)

这篇关于使用介于 2 个日期参数之间(包括 2 个日期参数)的日期填充临时表的最简单方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！