MySQL Select Date Equal to Today (having datetime as the data type)
MysqlMysql Problem Overview
I'm trying to run a mysql select statement where it looks at today's date and only returns results that signed up on that current day. I've currently tried the following, but it doesn't seem to work.
SELECT users.id, DATE_FORMAT(users.signup_date, '%Y-%m-%d')
FROM users
WHERE users.signup_date = CURDATE()
I've modified my SELECT statement to this, thanks guys.
SELECT id FROM users WHERE DATE(signup_date) = CURDATE()
Mysql Solutions
Solution 1 - Mysql
SELECT users.id, DATE_FORMAT(users.signup_date, '%Y-%m-%d')
FROM users
WHERE DATE(signup_date) = CURDATE()
Solution 2 - Mysql
This query will use index if you have it for signup_date field
SELECT users.id, DATE_FORMAT(users.signup_date, '%Y-%m-%d')
FROM users
WHERE signup_date >= CURDATE() && signup_date < (CURDATE() + INTERVAL 1 DAY)
Solution 3 - Mysql
Sounds like you need to add the formatting to the WHERE:
SELECT users.id, DATE_FORMAT(users.signup_date, '%Y-%m-%d')
FROM users
WHERE DATE_FORMAT(users.signup_date, '%Y-%m-%d') = CURDATE()
Solution 4 - Mysql
You can use the CONCAT with CURDATE() to the entire time of the day and then filter by using the BETWEEN in WHERE condition:
SELECT users.id, DATE_FORMAT(users.signup_date, '%Y-%m-%d')
FROM users
WHERE (users.signup_date BETWEEN CONCAT(CURDATE(), ' 00:00:00') AND CONCAT(CURDATE(), ' 23:59:59'))
Solution 5 - Mysql
This is a simple code to find date
SELECT users.id, DATE_FORMAT(users.signup_date, '%Y-%m-%d')
FROM users
WHERE DATE_FORMAT(users.signup_date, '%Y-%m-%d') = CURDATE()