1

How would I cast a column value (a "non-MySQL" DATE string retained as a VARCHAR) to a "MySQL date" so it can be used for sorting?

I have dates in the format of 06-02-2019 (6 feb) but MySQL itself doesn't seem to recognize this by just using the default DATE() function.

What I currently have is: SELECT * FROM Services ORDER BY Date(nextdate) DESC;

  • Is nextdate a column field of a date/datetime/timestamp type? If so you shouldn't be doing anything in the ORDER BY, the internal format knows how to order. Use DATE_FORMAT to display the date in whichever way you want in the result part of the select. – danblack Feb 14 at 22:14
  • @danblack sorry for not clarifying it, I meant that it was a normal string – Luc H Feb 15 at 8:46
  • changing it would be a good idea. Using generated columns you could keep the same column name and incrementally change to use a Date formatted column. – danblack Feb 16 at 1:15
2

You are looking for the STR_TO_DATE() function. MySQL expects by default dates in ISO format.

In your particular case it would be something like:

SELECT * FROM Services ORDER BY STR_TO_DATE(nextdate, %d-%m-%Y) DESC;

%d-%m-%Y or %m-%d-%Y, whatever is the "wrong" format you are using.

Using ISO dates (either strings or, preferably, timestamps) plus an index will have the efect that it will speed up your queries.

3

I have dates in the format of 06-02-2019

Unwritten Rule of Databases - Dates have no format.

Not any that you or I need to worry about, anyway.

BTW; When is "06-02-2019"? June 2nd or the 6th of February?

The ordering rules for Strings and Dates are [very] different.

As we all know 
   31/12/2018 < 01/01/2019
but 
   "31/12/2018" > "01/01/2019"   (because "3" > "0")

If you want to do "date" things with a field then it really, really ought to be a Date field. Trying to do anything else is just asking for trouble. Trying to do any sort of conversion "on the fly" is problematic ("dodgy" data loses data or breaks the query completely) and will be slow, having to run the conversion function on each and every row in the table (i.e. table-scanning!).

  • 1
    6 feb, clarified it a bit more in the question. The ordering that you are talking about is indeed the issue that i had which is logical when you think about it. – Luc H Feb 15 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.