ORDER BY in string date

Asked

Viewed 422 times

1

I have a table in the database, novidades, where I have a column with the name date is being stored as a string in the format dd-mm-yyyy, but I would like to sort by date based on this value, I calculate I have to convert to date/datetime at the time of select.

What I’m trying to do now is no more than this:

Novidade::orderBy('date', 'DESC')->get();

However it does not result, the ordering comes out wrong understandably.

NOTE: I can’t move the table and change the column type.

php sql laravel laravel-5.3

1 answer

1


Utilize orderByRaw with the function of your bank, for example, to utilize str_to_date to write the text-to-date conversion function, example:

Novidade::orderByRaw("STR_TO_DATE(date,'%d/%m/%Y') DESC")->get();

Observing: the good thing would be that the die had the field in the correct format, ie that it was converted to date/datetime would eliminate this problem, because, a function will have higher cost in the execution of its SQL.

References:

  • Virgilio, sorry, is there any similar function? It must have to do with mysql versions, because on site gives me the error SQLSTATE[HY000]: General error: 1 no such function: STR_TO_DATE (SQL: select * from "novidades" where "novidades"."deleted_at" is null order by STR_TO_DATE(date,'%d/%m/%Y') DESC). But in production it’s all right

  • @Hulahula this SQL is not from postgresql? What is your Database please?

  • It is mysql in production Virgilio, on site is sqlite, I apologize for the delay in reply, but only now I saw

  • @Hulahula then ta right. agree, another thing production and development is copy so different banks is a mistake should be all Mysql! understood.

  • The error only gives in place, with sqlite, the function should not exist in sqlite with this name. But no problem, what matters is in same Prod

Browser other questions tagged php sql laravel laravel-5.3

You are not signed in. Login or sign up in order to post.