MySQL convert to datetime syntax error: unexpected IDENT_QUOTED

Question

We have the following query that runs perfectly in MSSQL but fails to run in 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

The error we get from MySQL says:

syntax error, unexpected IDENT_QUOTED

This error occurs on the dateVal variable on the first line: "Select CONVERT(datetime, dateVal)."

If we remove the first CONVERT the error then moves to the next CONVERT on the following line. So, obviously, there seems to be an error with our datetime conversion. Not sure what we're doing the wrong though, any ideas out there? Thanks all.

Answer 1

I prefer to use CAST, but as others have said, you need to specify the type after the field like this:

convert(DateClickUTC,datetime)

Here is a working example using 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

BTW -- You actually don't need the subquery in this case -- this should work as well:

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