问题:

I have been looking all over the web for this answer but dont understand all the things that were written. I try to do the from unix_timestamp() but it returns the wrong date. What I have done is import a xls file into the database. It converted the date to a 5 digit number. Is there a simple way? Any help would be great. This is what I have tried. I tried changing the date format in the excel file to YYYY-MM-DD and this did not work.

回答1:

To go from Excel values, try this:

select date('1899-12-30') + interval <number> days

For instance:

select date('1899-12-30') + interval 40000 days

Returns July 6, 2009, just as it does in Excel.

In other words, you can bring the value in as an integer and then do the conversion in SQL.

I'm actually a little surprised because the 0 date on Excel is essentially Jan 0, 1900, which is Dec 31, 1899. I suspect there is an issue with the lack of leap year in 1900. Excel has the value of "60" represented as Feb 29,1900. This is amusing, I hadn't realized that Excel had this bug.

So, the above code works, for all values greater than 61.

回答2:

Try This

        $date_format = floor($excelDateTime);
        $time_format = $excelDateTime - $date_format;
        $mysql_strdate = ($date_format > 0) ? ( $date_format - 25569 ) * 86400 + $time_format * 86400 :    $time_format * 86400;
        $mysql_date_format = date("Y-d-m", $mysql_strdate);