Search code examples
mysqlsqlyog

> date arguments mysql

I'm trying to create a query that will pull based on the current day. I don't know the argument in the WHERE, Currently, I am using the date as a string.

SELECT`u.firstname AS `user`,COUNT(acc.submit_dt) AS `submitted`
FROM asset_camera_check AS acc JOIN users u ON u.userid = acc.user_id 
WHERE(acc.submit_dt = currentdate AND 
      acc.submit_dt < '2016-3-23 6:00:00'GROUP BY u.userid
Solution

  • Your WHERE clause needs a closing parenthesis (or just get rid of the opening parenthesis - you don't need parentheses here).

    Syntax aside, if what you're trying to do is include records between 00:00 (inclusive) and 06:00 (exclusive) for "today", try something like this:

    SELECT
  u.firstname AS `user`,
  COUNT(acc.submit_dt) AS `submitted`
FROM asset_camera_check AS acc
JOIN users u ON u.userid = acc.user_id
WHERE acc.submit_dt >= current_date
  AND acc.submit_dt < current_date + interval 6 hour
GROUP BY u.userid