datetime as a parameter of stored procedure

Question

I have this code snippet with a stored procedure Read_records_from_to

        cleaned_data = from_to_form.cleaned_data
        with connections["mssql_database"].cursor() as cursor:
            cursor.execute("Read_records_from_to '2021-12-01 07:55:39.000', '2021-12-14 07:55:39.000'")
            result = cursor.fetchall()


class FromToForm(Form):
    start_date = DateField(widget=AdminDateWidget())
    start_time = TimeField(widget=AdminTimeWidget())
    end_date = DateField(widget=AdminDateWidget())
    end_time = TimeField(widget=AdminTimeWidget())

The stored procedure takes to parameters from_datetime and to_datetime. I'd like to assign it values taken from FromtoForm. How can I do this?

I tried

start = datetime.combine(from_to_form.cleaned_data['start_date'], from_to_form.cleaned_data['start_time']).utcnow().isoformat()
end = datetime.combine(from_to_form.cleaned_data['end_date'], from_to_form.cleaned_data['end_time']).utcnow().isoformat()
context['start'] = start
with connections["mssql_database"].cursor() as cursor:
   cursor.execute("EXEC Readrecords_from_to @dt_from='start' ,@dt_to='end'")
   result = cursor.fetchall()

according to this answer. But it ended with error

Django Version: 2.2.4
Exception Type: DataError
Exception Value:    
('22007', '[22007] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Conversion failed when converting date and/or time from character string. (241) (SQLExecDirectW)')

score 0 · Answer 1 · answered Dec 15 '21 at 21:49

The error was in the execute statement. This is the right code.

start = datetime.combine(from_to_form.cleaned_data['start_date'], from_to_form.cleaned_data['start_time']).isoformat()
end = datetime.combine(from_to_form.cleaned_data['end_date'], from_to_form.cleaned_data['end_time']).isoformat()
with connections["mssql_database"].cursor() as cursor:
  cursor.execute("EXEC Read_records_from_to @dt_od='%s', @dt_do='%s'" % (start, end))
  result = cursor.fetchall()
  context['result'] = result

datetime as a parameter of stored procedure

1 Answers1