Importing dates from excel to MySql to PHP.

[webmaster1]

I'm importing an excel file into a table for outputting into a php page.

 

In excel, my date reads:

4/19/2010

 

If the destined field is set as datetime, will PHP/MySql be able to recognize it as date?

 

[Ken2k7]

Please read this - http://dev.mysql.com/doc/refman/5.0/en/datetime.html

 

[dotMoe]

If you email me tomorrow;

I have a function at the office that converts it from "excel" time, to a timestamp if you want.

[webmaster1]

If you email me tomorrow;

I have a function at the office that converts it from "excel" time, to a timestamp if you want.

 

Sent you a PM.

[webmaster1]

Please read this - http://dev.mysql.com/doc/refman/5.0/en/datetime.html

 

 

Have done. I've just tried inserting YYYY/MM/DD and  YYYY/DD/MM into a timedate field but I'm just ending up with zeros.

[webmaster1]

Formatted date in excel to match YYYY-MM-DD HH:MM:SS.

 

Working fine now. I was hoping MySql would have been able to pick up on a variety of formats for datetime field inserts.

 

[PFMaBiSmAd]

The mysql STR_TO_DATE() function can produce a DATETIME value from any other data/time format.

 

Your use of YYYY/MM/DD should have worked, depending on if the MM/DD had leading zero's and what your code actually was doing.

[webmaster1]

Thanks for that function. I was just using a regular insert but no, I did not have preceding zeros for day/month.

[webmaster1]

I've imported the csv file using import on phpmyadmin but the dates are showing in zero's.

 

Despite formating the dates correctly in excel, csv insists on formatting the date as 4/13/2010  12:00:00 AM.

 

How do I correct this?

[webmaster1]

Found a quirky solution:

http://forums.devshed.com/mysql-help-4/import-csv-date-to-mysql-673242.html

 

Apparently, each time the csv file is open, the format of the date is reset. In other words, once the csv file is saved from excel, it can't be opened in excel prior to uploading it.

 

I'm just glad to find the solution. I'd never have guessing that thiswas the issue.