I am trying to update the DB table using convert(datetime,'19-01-16 11:34:09 PM',5), but when I pass this as string to insert into DB table I'm getting the data type conversion error.
Conversion failed when converting date and/or time from character string.
insert into Run (RunID, StartDate)
values ('19012016',"convert(datetime,'19-01-16 11:34:09 PM',5)")
You are getting an error because you are passing treating it as a string value. Instead:
insert into Run(RunID, StartDate)
values ('19012016', convert(datetime, '19-01-16 11:34:09 PM', 5))
That is, you had too many quotes. In Python, this would typically be written as:
"""insert into Run(RunID, StartDate)
values ('19012016', convert(datetime, '19-01-16 11:34:09 PM', 5))"""
If you want to insert values:
"""insert into Run(RunID, StartDate)
values ('{0}', convert(datetime, '{1}', 5))""".format("19012016", "19-01-16 11:34:09 PM")
I would suggest that you use a more common format such as YYYY-MM-DD HH:MM:SS, if you have a choice of date representation.
Converting string values to datetime values using T-SQL can be error-prone, especially if you have to consider different locales and timezones.
Instead of using T-SQL for the conversion, use parameters with native Python datetime values. Python offers more flexibility with datetime parsing and formatting than T-SQL.
See my answer here for an example for using Python datetime values with pyodbc parameters.
