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?

Answers


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.

Try

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')

Need Your Help

Linux Kernel - What does it mean to "put" an inode?

linux linux-kernel kernel inode

I saw the following comment atop the iput function: