Question: 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'

```

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

```
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 -

1. Add an extra column to store the data temporarily
2. Update the table and copy the current date column value (formatted
DB friendly date) into the new temp column.
3. Remove the old column
4. Change the column name to the previous name so all existing queries work.

Then your query is as simple as

```
SELECT * FROM `tablename` where date between '2017-09-01' AND '2017-10-01'

```