Tag Archive for excel

Converting Excel dates and times to SQL

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:

date and time

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!