MySQL转换为日期时间语法错误: unexpected IDENT_QUOTED

Question

我们有以下查询在 MSSQL 中完美运行但在 MySQL 中无法运行:

select CONVERT(datetime, dateVal) as DateOccurred, itemID, COUNT(*) as Hits from (
select itemID, CONVERT(datetime, DateClickUTC) as dateVal
from tb_items
where DateClickUTC >= '2008-06-03 22:00:28.893' and DateClickUTC <= '2013-06-03 22:00:28.893'
group by CONVERT(datetime, DateClickUTC), UserID, itemID) as a
group by a.dateVal, itemID

我们从 MySQL 得到的错误是:

syntax error, unexpected IDENT_QUOTED

此错误发生在第一行的 dateVal 变量上:“Select CONVERT(datetime, dateVal)。”

如果我们删除第一个 CONVERT，则错误将移动到下一行的下一个 CONVERT。因此，很明显，我们的日期时间转换似乎存在错误。不确定我们做错了什么，有什么想法吗？谢谢大家。

Answer 1

我更喜欢使用CAST，但是正如其他人所说，您需要像这样在字段后指定类型:

convert(DateClickUTC,datetime)

这是一个使用 CAST 的工作示例:

select  a.dateVal as DateOccurred, itemID, COUNT(*) as Hits 
from (
  select itemID, cast(DateClickUTC as datetime) as dateVal
  from tb_items
  where DateClickUTC >= '2008-06-03 22:00:28.893' and DateClickUTC <= '2013-06-03 22:00:28.893'
  group by cast(DateClickUTC as datetime), UserID, itemID
) as a
group by a.dateVal, itemID

• SQL Fiddle Demo

顺便说一句——在这种情况下你实际上不需要子查询——这也应该有效:

select cast(DateClickUTC as datetime) as DateOccurred, 
   itemID, 
   COUNT(*) as Hits 
from tb_items
where DateClickUTC >= '2008-06-03 22:00:28.893' and DateClickUTC <= '2013-06-03 22:00:28.893'
group by cast(DateClickUTC as datetime), itemID