Laravel excel maatwebsite import, date column in excel cell returns as unknown format number. How to solve this?

Question

how to solve date format?

Error:

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date value: '43112'

My import class

 public function model(array $row)
{
    return new Agreement([
        'code' => $row[0],
        'start_date' => $row[1], 
        'end_date' =>$row[2],            
    ]);
}

what format $row[1] and $row[2] has and what data type you have for start_date and end_date in database — John Lobo, Jul 04 '21 at 09:18

score 2 · Answer 1 · edited Jul 04 '21 at 09:28

2

The datetime format is wrong, as the error says. It would be helpful to see what format you tried to use an input, but generally speaking the default format for date in mysql is Y-m-d, and for datetime it is Y-m-d H:i:s.

Try changing your format to comply with either of stated two formats and see what happens.

edited Jul 04 '21 at 09:28

Dharman

30,962
25
85
135

answered Jul 04 '21 at 09:22

R_Dax

706
3
10
25

John Lobo · Answer 2 · 2021-07-04T10:04:22.847

2

Convert date to mysql format using Carbon

return new Agreement([
        'code' => $row[0],
        'start_date' => Carbon::createFromFormat("d-M-Y",$row[1])->format("Y-m-d"), 
        'end_date' =>Carbon::createFromFormat("d-M-Y",$row[2])->format("Y-m-d"),            
    ]);

Also import Carbon

use Carbon\Carbon;

Updated If it has null value in excel then

return new Agreement([
        'code' => $row[0],
        'start_date' =>!empty($row[1])?Carbon::createFromFormat("d-M-Y",$row[1])->format("Y-m-d"):null, 
        'end_date' =>!empty($row[2])?Carbon::createFromFormat("d-M-Y",$row[2])->format("Y-m-d"):null,            
    ]);

edited Jul 04 '21 at 10:04

answered Jul 04 '21 at 09:24

John Lobo

14,355
2
10
20

I try this i have this error `The separation symbol could not be found` – Ezatullah Tabish Jul 04 '21 at 09:27
@EzatullahTabish is start_date and end_date are date type in db ? then it should work .is it null value exist in any row of $row[1] and $row[2] ? – John Lobo Jul 04 '21 at 09:58
@EzatullahTabish if start and end date in excel are null then you can check null value .i have updated answer – John Lobo Jul 04 '21 at 10:04

Laravel excel maatwebsite import, date column in excel cell returns as unknown format number. How to solve this?

2 Answers2