Sqlalchemy And Sql Server Datetime Field Overflow

I'm using SQLAlchemy to connect to a SQL Server database. I'm trying to insert an object into a table from my python script and it's failing. I'm receiving the error: (pyodbc.Data

Solution 1:

The corresponding date time field in the SQL Server table is of type datetime2.

Can it be that SQL Alchemy still builds that value as type DATETIME without taking into account corresponding type in a destination table?

datetime T-SQL:

Date range: January 1, 1753, through December 31, 9999:

Another reason, and seems it is root of the issue, is related to ODBC and DATETIME2: error 22008: Datetime field overflow when inserting a record with datetime2 field via ODBC

Earlier ODBC drivers for SQL Server could infer the server type (datetime or smalldatetime) from the scale (which had to be 0 or 3) and so could be more relaxed than SQL Server 2008 Native Client. The default scale for OdbcParameter is 0, and so earlier drivers could assume the server type must be smalldatetime and ignore any fractional seconds. With the introduction of datetime2 and a user defined scale of between 0 and 7 the driver can no longer infer the type from the scale and has to default to the richest type, datetime2. When the actual server type is not datetime2 there will be a server side conversion from datetime2 to the actual server type. I apologise for the invonvenience this has caused you, but we had little choice and the new behavior is documented.

So seems like ODBC, starting with a release SQL Server 2008, internally change a scale of DATETIME2.

I suggest to follow advice from this thread and switch from ODBC to a native SQL Server Client:

import sqlalchemy as sql
connectionString = 'mssql+pyodbc://username:password@my_server/my_database_name?driver=SQL Server Native Client 10.0'
engine = sql.create_engine(connectionString)