how to use dayofmonth function using node mysql

Question

I am trying to use a sql query which looks like:

'SELECT day(created), hour(created), minute(created), count(*) FROM `delivery` WHERE     `type_id`=1 AND `created`>=`2014-05-19 00:00:00` AND `created`<=`2014-05-19 23:59:59` GROUP BY     day(created),hour(created),minute(created) ORDER BY
day(created),hour(created),minute(created);

This runs perfectly in sequel pro, but results in the following error in express:

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'query' at line 1 at Query.Sequence._packetToError

Any help will be highly appreciated.

Thanks in advance!

Answer 1

When you use datetime values in your MySQL queries, you have to use

'2014-05-19 00:00:00' //(surrounded by '')

and NOT

`2014-05-19 00:00:00` //(surrounded by ``)

So, your query should look like this:

SELECT 
  day(created), hour(created), minute(created), count(*)
FROM
  delivery
WHERE
  type_id = 1
    AND created >= '2014-05-19 00:00:00'
    AND created <= '2014-05-19 23:59:59'
GROUP BY day(created) , hour(created) , minute(created)
ORDER BY day(created) , hour(created) , minute(created);