Error converting data type nvarchar to Datetime While Calling Store Procedure in Django
I am trying to pass datetime value to the store procedure of mssql but getting this error:
django.db.utils.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Error converting data type nvarchar to datetime. (8114) (SQLExecDirectW)')
Here is the code:
fromDate = datetime(2022, 1, 1)
toDate = datetime(2022, 12, 31)
dealershipId = request.GET['dealershipId']
cursor = connection.cursor()
cursor.execute(f"EXEC proc_LoadJobCardbyDateRange [@Fromdate={fromDate}, @Todate={toDate}, @DealershipId={dealershipId}]")
result = cursor.fetchall()
I have tried to pass datetime object in order to execute store procedure. I also tried to pass datetime as string but the same error persists. I also tried different formats but it didn't work as mentioned in some solutions that I have searched for.
Please guide me as to why I am having this issue and what's the correct solution to this can be?
The way you are building your string seems wrong, searching let me to change your string to this:
cursor.Execute('EXEC LoadJobCardbyDateRange @Fromdate = ?, @Todate=?, @DealershipId=?', fromDate, '2022-12-31', dealershipId)
See this link: Django execute stored procedure - different data types, multiple parameters and different columns from model