[SOLVED] Date substitution markers in SQL Server

Date substitution markers in SQL Server

I am facing an issue in processing the raw input datetime ('06/17/2023, 02:41:38') from tuple into SQL Server table which is define as datetime.

My SQL:

     my_sql = insert into @my_table
        (
            ename
            , eid
            , eloc
            , ejoindate

        )
        values (%s,%s,%s,%s)

cursor.execute(my_sql, ('ram', 'ABC001', 'India', '06/17/2023, 02:41:38')

On successful run, the col ejoindate should have the value like this '2023-06-17 02:41:38.000'

getting the error

(242, b'The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n')

Any suggestions to deal with datetime conversion or something?

Solution

I still need one more point or I would have used a comment.

Take out the comma between the data and time and it should work provided the computer is set for US date format.

cursor.execute(my_sql, ('ram', 'ABC001', 'India', '06/17/2023, 02:41:38')

Maybe something like this?

values (%s,%s,%s, REPLACE(%s, ',', ''))