Using Python to insert Excel DATE (not datetime) data into a SQL Server

Question

I am trying to use Python to import Excel workbook data into SQL Server, but under the TERM_DATE column when I tried to import the values it said it was incompatible with float datatype.

What the column looks like in Excel:

TERM_DATE
43101
43101
43101
43101
43132

What I need the SQL Server column to look like

TERM_DATE
2018-01-01
2018-01-01
2018-01-01
2018-01-01
2018-02-01

Extra info:

TERM_DATE is a column within the table I created is a date datatype inside SQL Server; and when I use Python to try to import the TERM_DATE column from Excel to SQL Server, I get this error:

DataError: ('22018', '[22018] [Microsoft][ODBC SQL Server Driver][SQL Server]Operand type clash: float is incompatible with date (206) (SQLExecDirectW); [22018] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)')

Excel will display 43101 as 2018-01-1, but what's actually in the cell is 43101 — John Vuu

David Browne - Microsoft David Browne - Microsoft · Accepted Answer · 2019-11-19T20:53:33

The shenanagans of converting float to and from datetime is not allowed for the newer date and datetime2 colums. Both SQL Server datetime and Excel dates are stored internally as floats, but Excel's "zero date" is 1900-01-00, while SQL Server uses 1900-01-01, and Excel has a bug where it treats 1900 as a leap year.

So if you subtract 2 from the Excel number, and then convert to a datetime then to a date you should get the same value.

eg something like:

insert into T(id, date) values (?, cast(?-2 as datetime))

Using Python to insert Excel DATE (not datetime) data into a SQL Server

1 Answers