>我有[A-NEST BY]
和[A-CUT BY]
字段都是datetime
字段为什么我在运行更新时得到
消息 206,级别 16,状态 2,过程 NSTBY,第 9 行操作数类型 冲突:日期与小音不兼容
SET [A-NEST BY] =
case DateAdd( day, -10, t1.[A-CUT BY] )
when 7 then DateAdd( day, -2, t1.[A-CUT BY] )
when 1 then DateAdd( day, -3, t1.[A-CUT BY])
else t1.[A-CUT BY]-10
end,
FROM WORKORDERS T1
INNER JOIN inserted i ON T1.[WORK ORDER #] = i.[WORK ORDER #]
END
使查询工作的实际逻辑如下所示:
UPDATE t1
SET [A-NEST BY] =
DATEADD
(
DAY,
-CASE DATEPART(WEEKDAY, t1.[A-CUT BY])
WHEN 3 -- For Tuesday, ten days prior is a Saturday, so take an extra day off.
THEN 11
WHEN 4 -- For Wednesday, ten days prior is a Sunday, so take an extra two days off.
THEN 12
ELSE 10 -- Default to ten days.
END,
t1.[A-CUT BY]
)
FROM WORKORDERS T1
INNER JOIN inserted i ON T1.[WORK ORDER #] = i.[WORK ORDER #]
也就是说,看起来你正在把它作为一个触发器。
例如,如果这是 WORKORDERS 表上的插入触发器,我建议不要使用触发器,而是使用计算列(除非 [A-NEST BY] 日期可以更改(。