CODEWITHSUNDEEP
mysqldatetime
Belgin Fish
Belgin Fish

Reputation: 19857

MySql query to select records with a particular date

so right now I'm storing a the date of each search done on my site like this

2011-06-07 21:44:01

now I'd like to execute a query to select all values where the date is equal to whatever, but only the year / month and day are taken into account, so it would be like

mysql_query("SELECT tag from tags WHERE date = '2011-06-07'");

but it shouldn't take into account the exact time (hour minute seconds), is there any way to do this?

Upvotes: 36

Views: 93751

Answers (5)

Stephan Georgiev
Stephan Georgiev

Reputation: 1

For me the following query works without a problem:

SELECT <col1>, <col2>, <col3> 
FROM `tablename`
where date like "2025-03-06%"
limit 20

Upvotes: 0

Eric Kirima
Eric Kirima

Reputation: 9

I know the answer am about to post is long overdue but it can help someone who may experience the same problem.

For my case I used:

SELECT * 
FROM tablename 
WHERE dateCol >= date("2017-11-01") 
AND dateCol < date("2017-11-01") + INTERVAL 1 DAY;

Its faster than using DATE() function.

If you are using a stored procedure, you could pass in the date as a string and supply it as an argument in place of ("2017-11-01")

Upvotes: 1

alex
alex

Reputation: 490657

You could use the DATE() function.

SELECT `tag`
  FROM `tags`
 WHERE DATE(`date`) = '2011-06-07'

However, for better performance you could use...

  WHERE `date` 
BETWEEN '2011-06-07'
    AND '2011-06-07 23:59:59'

Upvotes: 74

Phphelp
Phphelp

Reputation: 1330

mysql_query("SELECT tag from tags WHERE DATE(`date`) = '2011-06-07'");

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date

Upvotes: 1

Sparkup
Sparkup

Reputation: 3754

This should work:

mysql_query("SELECT tag from tags WHERE date LIKE '2011-06-07%'");

Upvotes: 7

Related Questions