I need to pull the records in date order, but having spent hours researching this and trying many approaches and variations in my code, I'm getting nowhere.
I need to sort on the left_employer_date field, which is a VARCHAR format. So I'm trying to convert the VARCHAR to DATE format to enable a date-based sort.
Here's my data:
Employer name, Position held, Hire date, Left employer date
Harteman Boat Yards, Foreman, 01/22/1998, 05/23/2005
Portland Light Rail Services, Shift Manager, 08/01/2005, 12/31/2018
US Forest Service, Game Warden, 01/12/2019, 04/23/2017
Here's my controller code to pull this data into a collection in left_employer_date order:
$employment_info = EmploymentHistory::orderBy(DB::raw("DATE_FORMAT(left_employer_date, \"%m/%d/%Y\")"), 'DESC')->get();
I've tried this in many variations and always get the returned data in the order entered (shown above), not sorted by date. Seems like no matter what I try, the orderBy is ignored.
My project is Laravel 6 on NGINX with MariaDB
Any suggestions are very welcome.
DATE_FORMAT transform a date to a string Tryp the inverse 'STR_TO_DATE(str,format)'
More docs https://mariadb.com/kb/en/str_to_date/