Search code examples
sql-serverpyodbc

SQL INSERT INTO DataError: (pyodbc.DataError) '22007

DataError: (pyodbc.DataError) ('22007', '[22007] [Microsoft][SQL Server Native Client 11.0][SQL Server]Conversion failed when converting date and/or time from character string. (241) (SQLExecDirectW)')

I am getting this error only on certain records. I don't understand why though. I only have one datetime field in the database. The parameter for the datetime field the insert is failing on is:

'2023-02-17 00:00:00.000000 +00:00'

Is there something wrong with this?

Solution

  • Yep. You can't have the offset when converting string to a datetime. It's allowed when converting to a datetime2 or datetimeoffset.

    select cast('2023-02-17 00:00:00.000000 +00:00' as datetime) --fails
go
select cast('2023-02-17 00:00:00.000000 +00:00' as datetime2) --works
go
select cast('2023-02-17 00:00:00.000000 +00:00' as datetimeoffset) --works

    so either remove the offset or use datetime2.