SQL 日期与 int 不兼容
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44528286/
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
date is incompatible with int
提问by johndoe
I get this error and although i tried to search for an existing answer, i could not implement is in mt query
我收到此错误，尽管我尝试搜索现有答案，但我无法在 mt 查询中实现
so i have got :
所以我有：
Msg 206, Level 16, State 2, Line 3 Operand type clash: date is incompatible with int
消息 206，级别 16，状态 2，第 3 行操作数类型冲突：日期与 int 不兼容
This is my query :
这是我的查询：
Select emp_desc, SUM(Price*Num_Of_Products)
from sales
Where sale_date between 2014-01-01 and 2017-01-01
GRoup By emp_desc
any suggestions? Many Thanks
有什么建议？非常感谢
回答by SqlZim
Single quotes around your date literals:
日期文字周围的单引号：
select
emp_desc
, sum(Price * Num_Of_Products)
from sales
where sale_date >= '20140101'
and sale_date < '20170101'
group by emp_desc
Also, betweenmight not be doing what you think it is doing there.
此外，between可能没有做你认为它在那里做的事情。
Reference:
参考：
The only truly safe formats for date/time literals in SQL Server, at least for
datetimeandsmalldatetime, are:YYYYMMDDandYYYY-MM-DDThh:mm:ss[.nnn]
- Bad habits to kick : mis-handling date / range queries - Aaron Bertrand
SQL Server 中日期/时间文字的唯一真正安全的格式，至少对于
datetimeandsmalldatetime来说是：YYYYMMDD和YYYY-MM-DDThh:mm:ss[.nnn]
-要踢的坏习惯：错误处理日期/范围查询 - Aaron Bertrand
回答by Esteban P.
either you need to use apostrophes for your dates
您需要在日期中使用撇号
Where sale_date between '2014-01-01' and '2017-01-01' GRoup By emp_desc
or the column sale_date is declared as int so you would have to use
或者列 sale_date 被声明为 int 所以你必须使用
Where sale_date between 20140101 and 20170101 GRoup By emp_desc
回答by Ven
Above definitely works or just by adding single quotes, you can also try this way if date is stored in datetime format
以上肯定有效，或者只是通过添加单引号，如果日期以日期时间格式存储，您也可以尝试这种方式
Select emp_desc, SUM(Price*Num_Of_Products)
from sales
Where convert(varchar(10),sale_date,120) between '2014-01-01' and '2017-01-01'
GRoup By emp_desc
回答by Mureinik
2014-01-01is an int - 2014minus1minus1, or 2012. If you surround these expressions with single quotes ('), they will become strings, and the implicit conversion from string to date should do the trick:
2014-01-01是一个整数2014减1减1，或2012。如果用单引号 ( ') 将这些表达式括起来，它们将成为字符串，并且从字符串到日期的隐式转换应该可以解决问题：
SELECT emp_desc, SUM(price * num_of_products)
FROM sales
WHERE sale_date BETWEEN '2014-01-01' AND '2017-01-01'
-- Here ----------^----------^-----^----------^
GROUP BY emp_desc
