如何修复错误 - 操作数类型冲突:日期与 int 不兼容

Question

INSERT INTO Buy
SELECT Title
,Type1
,Tedat
,DATEADD(DAY, - 2, DATEADD(YEAR, - 1, Tarikh))
,Descrip
FROM Buy
WHERE (
Tarikh BETWEEN 2019-03-21
AND 2020-03-19
)

Answer 1

您需要将日期括在引号中，因为没有它们，它们将被评估为一系列整数，这与WHERE子句中的日期无法比较：

INSERT INTO Buy 
SELECT      Title,
Type1,
Tedat,
DATEADD(DAY,-2,DATEADD(YEAR,-1,Tarikh)),
Descrip 
FROM        Buy
WHERE       Tarikh BETWEEN '2019-03-21' AND '2020-03-19'

Answer 2

您在日期上缺少引号

INSERT INTO Buy SELECT  Title,Type1,Tedat,DATEADD(DAY,-2,DATEADD(YEAR,-1,Tarikh)),Descrip FROM Buy
WHERE(Tarikh BETWEEN '2019-03-21' AND '2020-03-19' )

Answer 3

您需要将日期括在引号中，例如'2019-03-21'而不是2019 - 03 - 21

INSERT INTO Buy
SELECT Title
,Type1
,Tedat
,DATEADD(DAY, -2, DATEADD(YEAR, -1, Tarikh))
,Descrip
FROM Buy
WHERE Tarikh BETWEEN '2019-03-21' AND '2020-03-19'

此外，您应该指定列以避免意外错误(插入不正确的列或只想插入许多列而不是所有列(

INSERT INTO Buy(column1, column2,.. )
SELECT  Title, Tedat, .. 
FROM    Buy

阅读以下帖子以更好地了解

插入到某些列中