Sql Server 操作数类型冲突：日期与 int 不兼容
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26019011/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sql Server Operand type clash: date is incompatible with int
提问by Niranga Sandaruwan
When I try to execute this code I get an error at the 'with DateDimension' line:
当我尝试执行此代码时，在“with DateDimension”行出现错误：
Msg 206, Level 16, State 2, Line 15
Operand type clash: date is incompatible with int
消息 206，级别 16，状态 2，第 15 行
操作数类型冲突：日期与 int 不兼容
This is the SQL query I am using:
这是我正在使用的 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
)
回答by AHiggins
Your problem is with the DateCalendarValue + 1portion. Try using DATEADD(), as below:
你的问题是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)
EDIT: I don't know if your original code was going to use the MAXRECURSIONoption or not, but if you didn't know already I would recommend you read this. Basically, in this circumstance it means that you can list out 1,000 dates with the CTE. If you need more than that, you'll have to change that 1000 to match your needs.
编辑：我不知道您的原始代码是否会使用该MAXRECURSION选项，但如果您还不知道，我建议您阅读此. 基本上，在这种情况下，这意味着您可以使用 CTE 列出 1,000 个日期。如果您需要更多，则必须更改 1000 以满足您的需要。
