datetime.datetime conversion to datetime

I have defined a column in SQL Server as ldate with datetime data type and accepting null values. I’m trying to send data from my local machine using python and pyodbc. I have date like 20-01-2015 in string format. When I try to send data it throws an error:

pyodbc.DataError: (‘22007’, ‘[22007] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Conversion failed when converting date and/or time from character string. (241) (SQLExecDirectW)’)

So I convert that string to datetime format using

datetime.datetime.strptime('20-01-2015', '%Y-%m-%d')

This is datetime.datetime type. When I insert this value its throw below error

pyodbc.ProgrammingError: (‘42000’, ‘[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot find either column “datetime” or the user-defined function or aggregate “datetime.datetime”, or the name is ambiguous. (4121) (SQLExecDirectW)’)`

I’m struck – any help?

Answer

You are missing the time in your datetime string. You are just inserting date which is throwing first error. You can solve this error by passing time with date string, see below example:

2015-01-20 00:00:00

Or you can use datetime module as below:

First make datetime object from the string:

datetime_obj = datetime.datetime.strptime('20-01-2015', '%d-%m-%Y')

Then while inserting this datetime object into database convert it into the datetime string by simply str function

datetime_str = str(datetime_obj)

Now use this datetime_str.