MYSQL DATE range Query with date format

Question

I have a MYSQL table with field name "date" in the formate "dd/mm";

I need to query results from 20/06 to 22/06 (3 days data), this is how I am trying to do it.

mysql> SELECT * FROM `wtt` WHERE date BETWEEN '20/06' AND '22/06';

But MYSQL is also selecting the dates 22/04, 22/05 and other entries.

Is there MYSQL query which will fetch me the right answer without the need to change the format in which I have already stored the information in the MYSQL table?

Thank you all in advance!

Jan · Accepted Answer · 2011-06-25 11:08:00Z

3

You should convert the dates to MySQL date types and then compare them.

You can convert the dates like this:

SELECT STR_TO_DATE(CONCAT('20/06', '/', EXTRACT(YEAR FROM CURDATE())),'%d/%m/%Y');

or simply

SELECT STR_TO_DATE('20/06','%d/%m');

for year 0000 if it doesn't matter.

Storing dates as strings has brought this problem on you. You should always use DATE type for storing dates.

edited Jun 25, 2011 at 11:08

answered Jun 25, 2011 at 10:56

Jan

2,2931 gold badge17 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chinnappa Over a year ago

I understand now, the above query results "0000-06-20". How would the query be to select the results to for a range of dates? I tried many combination none of them worked :( I'm new to this, thank you for your patience.

Chinnappa Over a year ago

I got it, SELECT * FROM wtt WHERE STR_TO_DATE(date,'%d/%m') BETWEEN '0000-06-20' AND '0000-06-22'; Thank you a ton!

Collectives™ on Stack Overflow

MYSQL DATE range Query with date format

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related