Mysql – Excel import to MySQL: Date inserted as 0000-00-00

dateMySQLphpexcel

I have used PHPExcel library to import an Excel file (.xlsx) and insert into MySQL. I'm facing two problems with the date field:

1- In the excel sheet I try to change the date format to the desired one. Upon saving, the cells show the format I need (yyyy/mm/dd) but when I double-click to edit them they again change back to how it was before (mm/dd/yyyy).

2- When the file is imported to MySQL through PHPExcel all the columns are inserted correctly except the date column which inserts 0000-00-00. The format in the Excelsheet is the same as in MySQL but it enters is all zeros.

Please help!

Best Answer

The value store in PHPExcel is an Excel serialized datetime value (a float, representing the number of days since 1/1/1900 or 1/1/9004 depending on whether Windows or Mac Calendar was used). Changing the format is simply changing the way this value is displayed.

You will either need to know in advance whether a cell contains a date, or test it using the PHPExcel_Shared_Date::isDateTime() method, and convert it appropriately. You can then either retrieve it using getFormattedValue() to return the value as a formatted string; or use the built-in conversion functions to convert it to a unix timestamp (PHPExcel_Shared_Date::ExcelToPHP()) or a PHP DateTime object (PHPExcel_Shared_Date::ExcelToPHPObject()) that you can then format using the standard PHP date formatting functions before using it in your MySQL INSERT/UPDATE statements.

Related Solutions

Sql – How to return only the Date from a SQL Server DateTime datatype

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

for example

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

gives me

2008-09-22 00:00:00.000

Pros:

No varchar<->datetime conversions required
No need to think about locale

Mysql – Should I use the datetime or timestamp data type in MySQL

Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native format. You can do calculations within MySQL that way ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.

Best Answer

Related Solutions

Sql – How to return only the Date from a SQL Server DateTime datatype

Mysql – Should I use the datetime or timestamp data type in MySQL

Related Topic