Laravel : Order by string date using query builder with MySql

Question

The table have a startDate column which is VARCHAR type, so need to get rows by ascending order using this column.

Tried this :

orderBy(DB::raw("DATE(startDate)"), 'ASC')

the above does not return correct order, how to pass string date in order by clouse?

Example:

'startDate' => '07-Nov-2017'

$items = DB::table("mytable")->orderBy(DB::raw("DATE(startDate)"), 'ASC')->where('userId','=',$userId)->get();

What is startDate format?

Laerte
– Laerte

2017-12-22 11:49:45 +00:00
Commented Dec 22, 2017 at 11:49 — Laerte
– Laerte, Commented Dec 22, 2017 at 11:49
Is it Y-m-d? Show a example of startDate content.

Laerte
– Laerte

2017-12-22 11:51:30 +00:00
Commented Dec 22, 2017 at 11:51 — Laerte
– Laerte, Commented Dec 22, 2017 at 11:51
Sorry for my mistake with MySQL!

Laerte
– Laerte

2017-12-22 12:04:58 +00:00
Commented Dec 22, 2017 at 12:04 — Laerte
– Laerte, Commented Dec 22, 2017 at 12:04
@Laerte - Yes it is Mysql, I updated my question.

Nagaraj Jayaraman
– Nagaraj Jayaraman

2017-12-23 04:50:50 +00:00
Commented Dec 23, 2017 at 4:50 — Nagaraj Jayaraman
– Nagaraj Jayaraman, Commented Dec 23, 2017 at 4:50
So, try the answer I posted, and check if it works...

Laerte
– Laerte

2017-12-23 17:45:00 +00:00
Commented Dec 23, 2017 at 17:45 — Laerte
– Laerte, Commented Dec 23, 2017 at 17:45

Laerte · Accepted Answer · 2017-12-22 12:00:17Z

8

You need to convert the date to MySQL format. Try to use DATE_FORMAT:

$items = DB::table("mytable")
           ->orderBy(DB::raw("DATE_FORMAT(startDate,'%d-%M-%Y')"), 'ASC')
           ->where('userId','=',$userId)
           ->get();

answered Dec 22, 2017 at 12:00

Laerte

7,0833 gold badges35 silver badges52 bronze badges

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

4 Comments

Nagaraj Jayaraman Over a year ago

@MKhalidJunaid - Its mysql.

M Khalid Junaid Over a year ago

@151291 In future please tag your question accordingly

Nagaraj Jayaraman Over a year ago

ErrorException in Builder.php line 414: Illegal offset type

Nagaraj Jayaraman Over a year ago

Why modulus here ? %d-%M-%Y

Mathieu Ferre · Accepted Answer · 2017-12-28 13:28:26Z

0

Gunaseelan was right, but with synthax errors, try this :

$items = DB::table("mytable")->where('userId','=',$userId)->orderByRaw("DATE_FORMAT('d-m-Y',startDate), ASC")->get();

edited Dec 28, 2017 at 13:28

answered Dec 22, 2017 at 15:56

Mathieu Ferre

4,4671 gold badge16 silver badges31 bronze badges

1 Comment

Nagaraj Jayaraman Over a year ago

I tried this but not correct - "DATE('d-m-Y',startDate), ASC" "DATE(startDate) ASC" "DATE('d-m-Y',strtotime(startDate)), ASC" "DATE(strtotime(startDate)), ASC" "DATE(strtotime(startDate)) ASC" "DATE(strtotime('startDate')) ASC"

Collectives™ on Stack Overflow

Laravel : Order by string date using query builder with MySql

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related