Ordering records using dates in Laravel eloquent

Question

I have a column move_out that stores date in this format - m/Y e.g 02/2020. I want to order the records in that table by the move_out column in descending order but my solution don't seem to be working. It does not arrange the records appropriately. This is what I am doing.

$data = User::orderBy('move_out', 'DESC')->get();

How do I solve this?

The datatype for the move_out column is string.

The simple answer is store dates in the database ALWAYS as DATE or DATETIME. If you want to show dates in any other way that is a job for the presentation layer# — RiggsFolly
– RiggsFolly, Commented Mar 7, 2020 at 16:04
If you want a reliable sort you are going to have to use some SQL functions in your query to convert that string into a valid MySQL date. — RiggsFolly
– RiggsFolly, Commented Mar 7, 2020 at 16:08

Ronak Dhoot · Accepted Answer · 2020-03-07 18:51:16Z

3

try STR_TO_DATE

$data = User::orderBy(DB::raw("STR_TO_DATE(CONCAT('01-', move_out),'%d-%m/%Y')"), 'DESC')->get();

edited Mar 7, 2020 at 18:51

answered Mar 7, 2020 at 16:35

Ronak Dhoot

2,3311 gold badge14 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

shekwo Over a year ago

It does not. Not even ordering

shekwo Over a year ago

I hope you are aware, the date is stored in this format - 02/2020. Hope it does not affect your answer?

Ronak Dhoot Over a year ago

@radioactive see screenshot

Strawberry Over a year ago

Please don't post screenshots of text

Ronak Dhoot Over a year ago

@Strawberry: removed

Foued MOUSSI · Accepted Answer · 2020-03-07 16:21:34Z

2

Try to use DATE_FORMAT:

$items = DB::table("users")
           ->orderBy(DB::raw("DATE_FORMAT(move_out,'%M/%Y')"), 'DESC')
           ->get();

answered Mar 7, 2020 at 16:21

Foued MOUSSI

4,8334 gold badges21 silver badges40 bronze badges

Collectives™ on Stack Overflow

Ordering records using dates in Laravel eloquent

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related