Geek Answers Handbook

SQL DATETIME Insert from Excel?

So, I have a rather strange problem, I have a column (say column A) in excel that has data that looks like this:

11/11/2015 10:14

I have many other columns, but in any case, in my SQL Insert statement in excel, the data (when copying) looks like this: +42105,4561921296

The = "INSERT INTO TABLE VALUES ('' & A1 &" ', Etc ....) "is in the" general "data format, and the Date column is in the" Custom "format, where it is the type M / DD / YYYY MM / HH inside.

The SQL column is of the DATETIME data type, so of course it does not accept the strange number it receives.

Any ideas? changing the format of the SQL INSERT column does not change the results.

+5
source share
2 answers

You are right - Excel formats change the way you display , not the base value of the cell. In this case, the cell value is the excel date time value, which is # days from 1/1900, with decimals for the time.

I would recommend using the Excel TEXT function to convert a numeric date value in Excel to a text string that can be inserted into SQL:

Instead:

 INSERT INTO TABLE VALUES ('"&A1&"', Etc....)"

Try:

 INSERT INTO TABLE VALUES ('"&TEXT(A1,"YYYY-MM-DD HH:MM")&"', Etc...)"
+7
source

The best format for inserting a date in a datetime column is to indicate the date and time as YYYY-MM-DD Minute: second or 2015-04-15 12:52

so to insert the date and time from Excel you can use this set of Excel functions: (where A1 contains the datetime time to save)

 =YEAR(A1)&"-"&RIGHT("00"&MONTH(A1),2)&"-"&RIGHT("00"&DAY(A1),2)&" "&RIGHT("00"&HOUR(A1),2)&":"&RIGHT("00"&MINUTE(A1),2)&":"&RIGHT("00"&SECOND(A1),2)
+4
source

More articles:


All Articles