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…