[FIXED] Losing microseconds when inserting date to database

Issue

I have this code snippet in a view:

time_from = datetime.strptime(req.POST['time_from'], "%Y-%m-%d %H:%M:%S.%f")

with connections["mssql_database"].cursor() as cursor:
                sql_statement = "EXEC SaveActivity @TimeFrom='%s'" % (time_from)
                print(sql_statement)
                cursor.execute(sql_statement)

It prints this SQL statement:

EXEC SaveActivity @TimeFrom='2021-12-01 08:34:54'

Microseconds are missing. They are zeros, but I need them in a database. How can I correct this?

Solution

Don't perform string formatting. Pass the data as parameter. By using string formatting, it will use the str(…), which is for a datetime not very precise. Use:

with connections["mssql_database"].cursor() as cursor:
    sql_statement = "EXEC SaveActivity @TimeFrom='%s'"
    cursor.execute(sql_statement, (time_from,))

Using parameters over string interpolation is not only better to prevent data loss, it also prevents SQL injection where one might pass, for example through a string, an SQL query that will then modify or leak the database.

Answered By - Willem Van Onsem