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.

What is the data type of the `DateClickUTC` field? Looking at your `WHERE` clause, it appears to already be a datetime so perhaps no real need to convert? — sgeddes, Jun 04 '13 at 22:39
http://dev.mysql.com/doc/refman/5.5/en/cast-functions.html#function_convert — martin clayton, Jun 04 '13 at 22:39
In MySQL the CONVERT function takes target datatype as its second argument, not its first. — martin clayton, Jun 04 '13 at 22:41
@sgeddes - The column is a datetime column so, yes, maybe we shouldn't be converting it at all. This is old code written by a developer that no longer works with us so I'm not sure if he had a purpose to that or not... — madeFromCode, Jun 04 '13 at 22:48

score 2 · Accepted Answer · answered Jun 04 '13 at 22:45

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

@madeFromCode -- np, glad we could help! – sgeddes Jun 04 '13 at 22:58 — sgeddes, Jun 04 '13 at 22:58

MySQL convert to datetime syntax error: unexpected IDENT_QUOTED

1 Answers1