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()
This question is related to
mysql
SELECT users.id, DATE_FORMAT(users.signup_date, '%Y-%m-%d')
FROM users
WHERE DATE(signup_date) = CURDATE()
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'))
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()
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)
Source: Stackoverflow.com