Search code examples
mysqldate-format

Order by DATE_FORMAT not working properly

SELECT  SUBSTR(`filename`, 1, 10) as filedate, `filename`, `filepath`
FROM  `files`  
WHERE `filepath` LIKE 'sites/default/files/news/%' 
ORDER BY DATE_FORMAT(filedate,'%m.%d.%Y') ASC

the file name incluedes the date in the first 10 characters ex. 06.28.2013-Title_of_file so I grab the substring and set it as filedate, filter with WHERE LIKE

Then I try and order by the date using date_format, but when I run query it does not order correctly.

Do I have to convert it to date format? If so then how do I do that within SQL?

Solution 
  • SELECT  STR_TO_DATE(SUBSTR(`filename`, 1, 10),'%m.%d.%Y') as filedate, `filename`, `filepath`
FROM  `files`  
WHERE `filepath` LIKE 'sites/default/files/news/%' 
ORDER BY filedate ASC

    This will convert the stripped string into a DATE object.