SQL SELECT - order dates with wrong format-CodePudding

I was tasked with ordering some entries in our web application. Current solution made by some other guy 10 years ago, is that there is a select on db and then it iterates and make table.

Problem is, that date is in dd-mm-yyyy format and in varchar data.

And not really sure, if I am brave enought to make changes to the database.

So is there some way to order it anyway within a select, some way to order it by the end meaby? Or only way without making some gruesome function in code is to change the db?

CodePudding user response：

You can use the STR_TO_DATE() function for this. Try

ORDER BY STR_TO_DATE(varcharDateColumn, '%d-%m-%Y')

It converts your character-string dates to the DATE datatype where ordering works without trouble.

As of MySQL 5.7 or later, you can add a so-called generated column to your table without touching the other data.

     ALTER TABLE tbl 
      ADD COLUMN goodDate 
              AS (STR_TO_DATE(varcharDateColumn, '%m-%d-%Y'))
          STORED;

You can even put an index on that column if you need to use it for searrching.

ALTER TABLE t1 ADD INDEX goodDate(goodDate);

CodePudding user response：

You can use STR_TO_DATE function, but this will work only for small tables(maybe thousands of records), on large data sets you will face performance problems:

SELECT * 
FROM (
  SELECT '01-5-2013' AS Date
  UNION ALL
  SELECT '02-6-2013' AS Date
  UNION ALL
  SELECT '01-6-2013' AS Date
) AS t1
ORDER BY STR_TO_DATE(Date,'%d-%m-%Y')

Long term solution should be conversion of that column to proper date type.