MySQL BETWEEN for dates saved as string

First of all, it is recommended to use the MySQL's DATE type for dates, selecting the date range would be easy and efficient. But if you have your own reason to use string type (like you are working with a specific calender and you don't have the converter), then you should consider followings:

• you told that you are using the dd/mm/yyyy format for dates but in your code you wrote 11/7/2009 which should be 11/07/2009
• In order to select range you should save your date like yyyy/mm/dd, specially when you put index on this filed, it will be high performance.
• You need not to save format charterers like '/' in database. you can format the output later and show the date in any order and format you want.

As the result I offer you the following solution:

Use the YYYYMMDD format to save the date. the select query will be something like:

SELECT *, DATE_FORMAT(time_added, '%d/%m/%Y') AS time_added2 FROM attr 
WHERE time_added between '20090711' AND '20140829';

As and alternative if you can not change the database, then the following query will work on the existing database (date saved in dd/mm/yyyy format):

SELECT * FROM attr WHERE 
CONCAT(SUBSTR(time_added, 7, 4), SUBSTR(time_added, 4, 2), SUBSTR(time_added, 1, 2)) 
BETWEEN '20090711' AND '20140829';