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

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得到的错误是:

语法错误，ident_quotes

这个错误发生在第一行的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