您的问题在于该
DateCalendarValue + 1部分。尝试使用
DATEADD()，如下所示：
declare @DateCalendarStart date, @DateCalendarEnd date, @FiscalCounter date, @FiscalMonthOffset int;set @DateCalendarStart = '2011-01-28';set @DateCalendarEnd = '2012-10-26';-- Set this to the number of months to add or extract to the current date to get the beginning -- of the Fiscal Year. Example: If the Fiscal Year begins July 1, assign the value of 6 -- to the @FiscalMonthOffset variable. Negative values are also allowed, thus if your -- 2012 Fiscal Year begins in July of 2011, assign a value of -6.set @FiscalMonthOffset = 3;with DateDimensionas( select @DateCalendarStart as DateCalendarValue, dateadd(m, @FiscalMonthOffset, @DateCalendarStart) as FiscalCounter union all select DATEADD(DAY, 1, DateCalendarValue), -- Using a DATEADD() function here works for SQL Server DATEADD(m, @FiscalMonthOffset, (DATEADD(DAY, 1, DateCalendarValue))) as FiscalCounter from DateDimension where DATEADD(DAY, 1, DateCalendarValue) < = @DateCalendarEnd)SELECt * FROM DateDimension OPTION (MAXRECURSION 1000)
编辑：我不知道您的原始代码是否要使用该
MAXRECURSION选项，但是如果您不知道我建议您阅读此内容。基本上，在这种情况下，这意味着您可以使用CTE列出1,000个日期。如果您需要的更多，则必须将其更改为1000以符合您的需求。
