Converting Excel dates and times to SQL
Posted by: Richard
July302008
Dates and times look great in Excel until you try to concatenate them within a formula. Then you see Excel's messy raw date and time values. I have found it necessary to use the YEAR, MONTH, DAY, HOUR, and TIME functions to extract these values and then format them for a SQL query.
For example:
First attempt (didn't work)
The result:
Boo!
Second attempt (worked!)
The result:
Yay!
tags: excel, mysql, date, time, datetime
For example:
First attempt (didn't work)
="INSERT INTO `tour_dates` SET `datetime`='"&D2&" "&E2&"';"The result:
INSERT INTO `tour_dates` SET `datetime`='38260 0.375';Boo!
Second attempt (worked!)
="INSERT INTO tour_dates SET `datetime`='"&YEAR(D2)&"-"&MONTH(D2)&"-"&DAY($D2)&" "&HOUR(E2)&":"&MINUTE(E2)&":00"&"';"The result:
INSERT INTO tour_dates SET `datetime`='2008-10-1 9:0:00';Yay!
tags: excel, mysql, date, time, datetime
Richard Kassissieh is Director of Information Services at 
RSS 2.0 