Issues Searching Records By Dates

hi everyone I am storing the bill date in mysql using varchar datatype and I save the column like this ‘dd/mm/yy’ ex 31/12/2013 what I want to do is search records by bill date b/w two dates ex 01/12/2013 to 31/12/2013 but the mysql produces wrong results. as I googled abt this issue and they told that it is due to varchar datatype for storing date. so what should I do which datatype shall I use? but still I want to store date in this format only (dd/mm/yy)

The data type have to be in date format and you can configure your query like;

SELECT *, DATE_FORMAT(column, ‘%d-%m-%Y’) As new_date_format FROM yourtable;

but I am using between condition. how to do that

It was an example…

Select * from yourtable where '30/12/2014' between DATE_FORMAT(date1column, '%d-%m-%Y') and DATE_FORMAT(date2column, '%d-%m-%Y');

is ur query works for the date1Column value like this 31/01/2014?

I didnt try it.

its ok thank you mcqueen.

Sorry, it will works for date1 and date2 columns…