CODEWITHSUNDEEP
mysqlsqldatabase
molleman
molleman

Reputation: 2946

Return all rows which are same day in MySQL

I store a date in my database as a string like this:

03/08/2013 --> 8th of march

I'm trying to select only the rows that are the same day as the current day:

SELECT * FROM wp_aerezona_booking WHERE DATE_SUB(CURDATE(),INTERVAL 1
                                         DAY) <= STR_TO_DATE(date, '%m/%d/%Y')

The above is what I tried, but it is returning a lot of results and should only return 1.

Upvotes: 0

Views: 1815

Answers (3)

Sharad
Sharad

Reputation: 3548

If you want the same date as today's date then you have to use "=" operator with.

SELECT * 
FROM wp_aerezona_booking 
WHERE  STR_TO_DATE(date, '%m/%d/%Y')= CURDATE()

Upvotes: 0

JodyT
JodyT

Reputation: 4412

This should work already:

SELECT * FROM wp_aerezona_booking 
WHERE STR_TO_DATE('03/08/2013', '%m/%d/%Y') = CURDATE();

By using the DATE_SUB you are subtracting1 day from the current day. You're not looking at today but yesterday. Also the <= makes you look at yesterday and all days before that.

Upvotes: 2

Explosion Pills
Explosion Pills

Reputation: 191749

Then you don't want <=, but you want =. The former will get all results if date is less than or equal to yesterday's date. I'm not sure that you even want the DATE_SUB either.

Upvotes: 1

Related Questions