convert datetime in SQL query

Question

I want to select records within a period of date. However, the corresponding data type in database is like '2017-11-13-05-36-25'. I need to convert it to datetime format first. The following SQL query is made using pyodbc:

connection = pyodbc.connect("Driver={\
ODBC Driver 13 for SQL Server};\
Server=server1,\
100;Database=my_db;\
Uid=abc;\
Pwd=123;\
Encrypt=yes;\
TrustServerCertificate=no;\
Connection Timeout=30;")

cmd = r'SELECT startDate FROM table1 where \
CONVERT (datetime, startDate, 120) between \
CONVERT(datetime, "2017-10-8 00:00:00", 120) and \
CONVERT(datetime,"2017-11-7 00:00:00", 120)'

a = pd.read_sql(cmd, con = connection)

After execution, I received the following error:

Error: ('42S22', "[42S22] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid column name '2017-10-8 00:00:00'. (207) (SQLExecDirectW)")

How can get the correct result?

Martin Smith · Accepted Answer · 2017-11-18 17:54:56Z

2

string literals are delimited by single quotes in SQL Server by default. Double quotes denote an identifier such as column or object name.

So instead of "2017-10-8 00:00:00" use '2017-10-8 00:00:00' and same for "2017-11-7 00:00:00".

this will fix the error you are receiving.

Invalid column name '2017-10-8 00:00:00'

The CONVERT (datetime, startDate, 120) will make your query unsargable by the way. It won't be able to do an index range seek on that column. startDate should use a suitable datatype (date/datetime/datetime2) such that it doesn't need to be converted at run time.

edited Nov 18, 2017 at 17:54

answered Nov 18, 2017 at 17:40

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mandy Over a year ago

Thank you, Martin. I want to select a certain period of startDate. Are there any methods to do that if I cannot change the datatype of the database table?

Collectives™ on Stack Overflow

convert datetime in SQL query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related