Changing date format – Laravel

I have a column in my table named expiry which has type VARCHAR. In the column, there is a mixture of date format like YYYY-MM-DD and MM-DD-YYYY.

How can i re-arrange the format MM-DD-YYYY to YYYY-MM-DD in my laravel view blade ?

View

 @foreach($product_select as $key => $product)
   <tr>
   <td>{{$product->name}}</td>
   <td>{{CarbonCarbon::parse($product->expiry)->isoFormat('YYYY MM DD')}}</td>
   </tr>
   @endforeach

In the view above, it throws an error

Could not parse ’02-27-2021′: DateTime::__construct(): Failed to parse time string (02-27-2021) at position 0

How can i solve this ?

Product Output

{#3504 ▼
  +"product_id": "999"
  +"id": 999
  +"name": "CONFIDO    CONFIDO TABS 60'S HIMALAYA"
  +"code": "P935"
  +"expiry": "03-31-2023"
  +"type": "standard"
}

Answer

I’m actually going to just suggest that you stop storing date information in a text varchar column. Instead, you should be using a proper MySQL date column. Here is how you can rectify the situation from MySQL:

ALTER TABLE product ADD COLUMN new_expiry DATE;

UPDATE product
SET new_expiry = CASE WHEN expiry REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}$'
                      THEN CAST(expiry AS DATE)
                      ELSE STR_TO_DATE(expiry, '%m-%d-%Y') END;

ALTER TABLE product DROP COLUMN expiry;
ALTER TABLE product RENAME COLUMN new_expiry TO expiry;

I wouldn’t even bother trying to manage this column from Laravel. Even if someone gives you an answer, it will be ugly and a lot of work, and also not best practice.