Sql Server操作数类型冲突:日期与int不兼容

Question

当我尝试执行此代码时，在"with DateDimension"行出现错误：

消息206，级别16，状态2，第15行
操作数类型冲突：日期与int 不兼容

这是我正在使用的SQL查询：

declare @DateCalendarStart  date,
        @DateCalendarEnd    date,
        @FiscalCounter      date,
        @FiscalMonthOffset  int;
set @DateCalendarStart = '2011-01-28';
set @DateCalendarEnd = '2012-10-26';

set @FiscalMonthOffset = 3;
with DateDimension //Error got this line 
as
(
    select  @DateCalendarStart as DateCalendarValue,
            dateadd(m, @FiscalMonthOffset, @DateCalendarStart) as FiscalCounter
    union all
    select  DateCalendarValue + 1,
            dateadd(m, @FiscalMonthOffset, (DateCalendarValue + 1)) as FiscalCounter
    from    DateDimension 
    where   DateCalendarValue + 1 < = @DateCalendarEnd
)

Answer 1

您的问题在于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 DateDimension 
as
(
    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列出1000个日期。如果你需要更多，你必须改变1000以满足你的需求。