ALTER TABLE ORDER BY str_to_date and time_to_sec

I am trying to re-order a table after importing a file but am getting a mysq开发者_Go百科l syntax error on this query:

ALTER TABLE tablename ORDER BY str_to_date(date, '%m/%d/%Y'), time_to_sec(time) ASC

Can anyone see anything wrong with this? this is the error I am getting:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(date, '%m/%d/%Y'), time_to_sec(time) ASC' at line 1

According to mysql manual, ALTER TABLE ORDER BY expects a col_name, but you are trying to order it using a function.

I suggest create 2 new columns, populate them with function results, and then order by them.

You might have to use a temp table for this since you're ordering by a function.

CREATE TABLE temp_tablename AS SELECT * FROM tablename;

TRUNCATE tablename;

INSERT INTO tablename SELECT * FROM temp_tablename;

DROP temp_tablename;

You could make the first statement a CREATE TEMPORARY TABLE, but if you lose your session you lose your data.

Did you try

ALTER TABLE tablename ORDER BY date, time ASC

I mention this because it might give you the order you need.

Otherwise you'll need to either do as German Rumm suggested, add columns with the correct datatype, or do your ordering when you do your SQL.

I think I resolved this issue. I was storing imported data in a temp table before moving it onto it's permanent table and needed to sort the data in the temp table first before inserting into the new table. so what I do instead is insert into the new table via a select statement which has the order by statement.

You are using an older version of mysql, STR_TO_DATE is only availabe from version 4.1.1 of MySQL.

Update MySQL version.