date between in mysql not correctly working

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.\n\nSTR_TO_DATE('01/09/2017', '%m/%d/%Y')\n",

You need to store dates as DATE type not VARCHAR or TEXT.\n\nAlso DB dates are in the format YYYY-MM-DD usually so you will need to adjust your query accordingly.\n\nDue to speed trying to use STR_TO_DATE is a terrible idea, better to convert once and then use MySQL as intended.\n\nBackup your data first and then I (think) the following will work\n\nBEGIN;\nALTER TABLE `tablename` \nADD COLUMN `new_date` DATE;\n\nUPDATE `tablename` \nSET `new_date` = STR_TO_DATE(`date`, '%d/%m/%Y');\n\nALTER TABLE `tablename` \nDROP COLUMN `date`;\n\nALTER TABLE `tablename` \nCHANGE COLUMN `new_date` `date` DATE;\nCOMMIT;\n\n\nStep By Step -\n\n\nAdd an extra column to store the data temporarily\nUpdate the table and copy the current date column value (formatted\nDB friendly date) into the new temp column.\nRemove the old column\nChange the column name to the previous name so all existing queries work.\n\n\nThen your query is as simple as\n\nSELECT * FROM `tablename` where date between '2017-09-01' AND '2017-10-01'\n",