Python SQL Server timestamp in where clause using timedelta

Question

Error when using timedelta from datetime.now() in SQL Server where clause

python 3.6

yesterday = datetime.now() - timedelta(days=1)

sql = "SELECT submit_dt, api_job_name, job_status, xml_record_count, x_successful_number, x_failed_number, " \
      f"job_run_time, mf_job_name FROM JOB_LOG where submit_dt > {yesterday}"

try:
    db = Database()
    db.cursor.execute(sql)
    rows = db.cursor.fetchall()

SQL ODBC Error: Incorrect syntax near '22' --- which is the time part of the datetime.

I've tried wrapping it in '' but then get convert from string error.

Parfait · Accepted Answer · 2019-05-08 15:40:39Z

1

Consider parameterizing your query without any need of string conversion of datetime or string interpolation including F-strings.

yesterday = datetime.now() - timedelta(days=1)

sql = """SELECT submit_dt, api_job_name, job_status, xml_record_count, 
                x_successful_number, x_failed_number, 
                job_run_time, mf_job_name 
         FROM JOB_LOG 
         WHERE submit_dt > ?"""

try:
    db = Database()
    db.cursor.execute(sql, yesterday)
    rows = db.cursor.fetchall()

edited May 8, 2019 at 15:40

answered May 8, 2019 at 15:22

Parfait

108k19 gold badges103 silver badges138 bronze badges

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

Comments

Abe Scott · Accepted Answer · 2019-05-08 14:37:57Z

0

The error was due to including the microseconds in the compare value. I was able to use:

yesterday_sql = yesterday.strftime("%Y-%m-$d %H:%M:%S")

answered May 8, 2019 at 14:37

Abe Scott

111 silver badge3 bronze badges

Collectives™ on Stack Overflow

Python SQL Server timestamp in where clause using timedelta

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related