MySQL Tip - Sorting date represented as a string

Sometimes, when designing your database table, you may use a string format to represent a date. For example, to represent Nov 5, 2014, you might use the format DD/MM/YYYY, resulting it to be stored as 05/11/2014.

However, when retrieving multiple rows contain dates, this might pose a problem when it comes to sorting the items based on dates.

To resolve this, convert the date string to datetime value using the MySQL STR_TO_DATE() function, together with the format string representing the date. The following statement allows you to sort the date correctly:

SELECT * FROM TABLE ORDER BY STR_TO_DATE(STARTDATE, '%d/%m/%Y')";