Laravel Excel import returning wrong date

Question

I have an Excel file, where I have a date column. The date looks like "17.07.2020".

I'm using Laravel and Maatwebsite Excel import. When I'm saving the imported data to MySQL, the date in MySQL is always "1970-01-01".

Here is the code, which I have now:

return new Product([
    .......
    'discountBeginning'  => date('Y-m-d', strtotime($row['discount_beginning'])
]);

I would like to format the date from Excel to "2020-07-17".

I made an answer, but read your question again. How is discountBeginning being stored in your database? — Kurt Friars
– Kurt Friars, Commented Jul 17, 2020 at 11:20
To be able to answer this definitely, you need to provide the output of $row['discount_beginning']? Looks like the strtotime() call is the bug here, since it is just returning the Unix Epoch Time (1970-01-01). Perhaps $row['discount_beginning']contains a format that strtotime() is unable to parse? — Torkil Johnsen
– Torkil Johnsen, Commented Jul 17, 2020 at 11:26
The output in the database is always "1970-01-01", even though the date in Excel is 17.07.2020 — Jakub Kříž
– Jakub Kříž, Commented Jul 17, 2020 at 11:42
I also got that 1970-01-01 date. What really worked for me is $your_date = PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($row['your_date']); from the comment section of this question. In the excel row, I got a string as 2022-05-29 (for example) and that worked! — Pathros
– Pathros, Commented Aug 19, 2022 at 17:38

VIKAS KATARIYA · Accepted Answer · 2020-07-17 11:33:29Z

3

Try this

$date = new DateTime($row['discount_beginning']);
echo $date->format('d.m.Y');

edited Jul 17, 2020 at 11:33

answered Jul 17, 2020 at 11:20

VIKAS KATARIYA

6,0153 gold badges19 silver badges37 bronze badges

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

4 Comments

Kurt Friars Over a year ago

I dont think this will work given the format in OPs excel file. ie) 6.5.2020 is it May 6th, or June 5th?

Kurt Friars Over a year ago

I am wrong DateTime has the same interpretation, d.m.Y.

VIKAS KATARIYA Over a year ago

Okay i think my updated answer is work for @Jakub Kříž

Pathros Over a year ago

I get this error: Failed to parse time string (44710) at position 4 (0)

Kurt Friars · Accepted Answer · 2020-07-17 11:29:37Z

0

You are using Laravel, so you already have access to the awesome Carbon library. For how data is formatted in your excel file (day.month.year), you can use it like:

return new Product([
    .......
    'discountBeginning'  => \Carbon\Carbon::createFromFormat('d.m.Y', $row['discount_beginning']),
]);

Once you have the carbon instance, you can format it however you would like according to php's date syntax like:

$date = \Carbon\Carbon::createFromFormat('d.m.Y', $row['discount_beginning']);
$date->format('Y-m-d');

edited Jul 17, 2020 at 11:29

answered Jul 17, 2020 at 11:18

Kurt Friars

3,7742 gold badges18 silver badges29 bronze badges

7 Comments

Jakub Kříž Over a year ago

I've tried your code, but it's returning an error "The separation symbol could not be found"

Jakub Kříž Over a year ago

I'm using Excel's default date format, which is exactly "17.07.2020"

Jakub Kříž Over a year ago

It won't even let me dd somehow. Always the same error "The separation symbol could not be found".

Jakub Kříž Over a year ago

Well, it looks like its grabbing the date in General format, where 17.07.2020 looks like 44029 in Excel, even though column format is date.

Jakub Kříž Over a year ago

Yes, the output in log was 44029. It's one of the formats in Excel, like for example Time, Currency, Date, Percentage etc. bettersolutions.com/excel/formatting/…

|

Ron · Accepted Answer · 2020-07-17 11:59:33Z

0

$date =  "17.07.2020";
$d2 = \Carbon\Carbon::createFromFormat('d.m.Y', $date)->format('Y-m-d');
dd($d2); // returns: "2020-07-17"

you can also try to append ->toDateString(); if your returned date is not a string. If it is already a string, this will fail.

answered Jul 17, 2020 at 11:59

Ron

6,8192 gold badges24 silver badges36 bronze badges

2 Comments

Pathros Over a year ago

What if I get the The separation symbol could not be found Data missing error?

Ron Over a year ago

check this: stackoverflow.com/questions/37957595/…

pankaj · Accepted Answer · 2024-12-26 17:04:28Z

-1

Try this. it will work sure. I tested with my code but note that the imported file should be in CSV format...

  Carbon::createFromFormat('d/m/Y', $row['discount_beginning'])->format('Y-m-d')

answered Dec 26, 2024 at 17:04

pankaj

1,97026 silver badges37 bronze badges

Collectives™ on Stack Overflow

Laravel Excel import returning wrong date

4 Answers 4

4 Comments

7 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

7 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related