Menu
I have a field containing dates in this format DD/MM/YYYY and I need to order the results DESC by this field, but it is saved as a VARCHAR and I cannot change this. Is there a workaround?
There really is no way for me to change the field type so please don't say this is a bad way to do this as I already know. I just need to know if it is possible.
Thanks for any help and advice in advance.
694
Use the ORDER BY keyword and the name of the column by which you want to sort. This way, you'll sort the data in ascending order by this column. You could also use the ASC keyword to make it clear that the order is ascending (the earliest date is shown first, the latest date is shown last, etc.).
MySQL uses yyyy-mm-dd format for storing a date value. This format is fixed and it is not possible to change it. For example, you may prefer to use mm-dd-yyyy format but you can't. Instead, you follow the standard date format and use the DATE_FORMAT function to format the date the way you want.
MySQL retrieves and displays DATE values in ' YYYY-MM-DD ' format. The supported range is '1000-01-01' to '9999-12-31' . The DATETIME type is used for values that contain both date and time parts.
You can do it by the following way,
SELECT ...
FROM ...
ORDER BY STR_TO_DATE(yourDate,'%d-%m-%Y') DESC
Using STR_TO_DATE:
http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_str-to-date
...
order by str_to_date(myCol, '%d/%m/%Y')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
