How to fetch data from database according to date using php mysql
SELECT * FROM `reg` WHERE registration_date BETWEEN '29 /01 /15' AND '30 /01 /15'
I am using this query to fetch data from the database, it gives me the data, but when I change the date, for example BETWEEN '29/01/15' AND '06/03/15', it show nothing. I am using the datatype varchar.
Can anyone help me solve this error?
From the MySQL manual: The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.
You need to change the format you use for the dates.
SELECT * FROM reg WHERE registration_date BETWEEN '2015-01-29' AND '2015-01-30'
Use WHERE condition as below as in table date is saved in "Y-m-d" format :
WHERE registration_date BETWEEN '2015-01-29' AND '2015-01-30'
Here is the solution for you, I have also use these method to get data using date, Please try it mayhelp you :-
$start = date('Y-m-d H:i:s', strtotime('29-01-2015')); $end = date('Y-m-d H:i:s', strtotime('30-01-2015')); SELECT * FROM `reg` WHERE registration_date >= $start AND registration_date <= $end;
Actually you are using data type Varchar so it will between and And will not work. it is showing your result because it comparing to binary digit .so it is showing you result between the two binary digits. You need to change your data type to Date and Time.
Your query only works because BETWEEN/AND in your case is only checking that the hexcode of the characters used in the string are between each other. You will also get a result for:
SELECT * FROM `reg` WHERE registration_date BETWEEN '29/dur' AND '30/3.13159'
But if you'd like to treat your query like actual dates, I'd suggest using:
SELECT * FROM `reg` WHERE str_to_date(registration_date,'%d/%m/%Y') BETWEEN str_to_date('29/01/15','%d/%m/%Y') AND str_to_date('30/01/15','%d/%m/%Y')