How to fields to date format in laravel mysql-CodePudding

I'm trying to convert the 2 fields into date using DB::raw and DATE_FORMAT in mysql but I'm getting null result. Please see my code below.

Code

$company = Company::select([
        DB::raw("DATE_FORMAT(CONCAT('1 ', month, ', ', year), '%M %d %Y') as date")
    ])
    ->limit(2)
    ->get();

Result

[
    {
        "date": null
    },
    {
        "date": null
    }
]

Data stored in db

 ----------------- 
| month    | year |
 ----------------- 
| January  | 2021 |
 ----------------- 
| March    | 2021 |
 ----------------- 
| February | 2022 |
 -----------------

CodePudding user response：

use STR_TO_DATE to generate date then format using DATE_FORMAT

$company = Company::selectRaw("DATE_FORMAT(STR_TO_DATE(CONCAT('1',month,year),'%d%M%Y'), '%d-%m-%Y') as date")
->limit(2)
->get();

Here STR_TO_DATE(CONCAT('1',month,year),'%d%M%Y') will generate MySQL formated date like 2021-01-01

Ref:https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_str-to-date

Ref:https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format