CODEWITHSUNDEEP
pythonsql-serversqlalchemy
t_warsop
t_warsop

Reputation: 1280

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.DataError) ('22008', '[22008] [Microsoft][ODBC SQL Server Driver]Datetime field overflow (0) (SQLExecDirectW)')

It looks like this is being caused by the following datetime object:

datetime.datetime(214, 7, 21, 0, 0)

... that's the 21st July 214

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

It looks like the conversion from python/SQLAlchemy to SQL Server isn't adding a '0' to beginning of the year value. I've confirmed this by the fact that I can manually add this date to the SQL Server using an INSERT statement with and without the leading '0'.

Is there a way to force the year part of the date into the correct format? Or is this being caused by something else?

UPDATE: From https://docs.sqlalchemy.org/en/latest/dialects/mssql.html I've found that you can specify the type of a column as DATETIME2 (for MS SQL) and I've updated the object mapping accordingly.

So before it was:

from base import Base
from sqlalchemy import Column, Integer, String, Numeric, DateTime

class Results(Base):
    __tablename__ = 'Result'

    dateTimeMinValue = Column(DateTime)
    dateTimeMaxValue = Column(DateTime)

And I've now updated it to:

from base import Base
from sqlalchemy import Column, Integer, String, Numeric
from sqlalchemy.dialects.mssql import DATETIME2

class Results(Base):
    __tablename__ = 'Result'

    dateTimeMinValue = Column(DATETIME2)
    dateTimeMaxValue = Column(DATETIME2)

But I still get the same error as before.

Upvotes: 3

Views: 10388

Answers (1)

Alexander Volok
Alexander Volok

Reputation: 5940

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)

Upvotes: 5

Related Questions