CODEWITHSUNDEEP
mysql
Jako
Jako

Reputation: 4901

MySQL Select Date Equal to Today (having datetime as the data type)

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()

Upvotes: 146

Views: 318500

Answers (5)

Devendra Sharma
Devendra Sharma

Reputation: 21

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()

Upvotes: 1

Tanakom Talawat
Tanakom Talawat

Reputation: 86

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'))

Upvotes: 4

Sergii Stotskyi
Sergii Stotskyi

Reputation: 5400

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)

Upvotes: 47

Barmar
Barmar

Reputation: 782653

SELECT users.id, DATE_FORMAT(users.signup_date, '%Y-%m-%d') 
FROM users 
WHERE DATE(signup_date) = CURDATE()

Upvotes: 279

Taryn
Taryn

Reputation: 247870

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()

See SQL Fiddle with Demo

Upvotes: 19

Related Questions