when I fetch data from table "like date from 01/09/2017 to 30/09/2017" then it's okey..
BUT When I am trying to fetch data from date 01/09/2017 to 01/10/2017 then its only showing the data of DATE 01/10/2017(not previous month data i.e 01/09/2017)
I am using MySQL Database.
SELECT * FROM `tablename` where date between '01/09/2017' AND '01/10/2017'
If you are saving the value as DATE format it should work. If not (you are saving the data as VARCHAR you can convert it to date and get the correct results.
STR_TO_DATE('01/09/2017', '%m/%d/%Y')
According to your example you have stored date as text so you need to apply STR_TO_DATE() to perform date operations
Try below query:
SELECT * FROM `tablename` where STR_TO_DATE(date,'%d/%m/%Y')between
STR_TO_DATE('01/09/2017','%d/%m/%Y') AND STR_TO_DATE('01/10/2017','%d/%m/%Y');
You need to store dates as DATE type not VARCHAR or TEXT.
Also DB dates are in the format YYYY-MM-DD usually so you will need to adjust your query accordingly.
Due to speed trying to use STR_TO_DATE is a terrible idea, better to convert once and then use MySQL as intended.
Backup your data first and then I (think) the following will work
BEGIN;
ALTER TABLE `tablename`
ADD COLUMN `new_date` DATE;
UPDATE `tablename`
SET `new_date` = STR_TO_DATE(`date`, '%d/%m/%Y');
ALTER TABLE `tablename`
DROP COLUMN `date`;
ALTER TABLE `tablename`
CHANGE COLUMN `new_date` `date` DATE;
COMMIT;
Step By Step -
Then your query is as simple as
SELECT * FROM `tablename` where date between '2017-09-01' AND '2017-10-01'