Getting a syntax error when trying to execute this SQL query in Python

Question

I'm trying to get the script to automatically set the query start time as 6am yesterday (and will set the end time as 6am today once I figure out the Start time error) but I am getting a syntax error (102, b"Incorrect syntax near '06'.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n").

If I take out the """+StartTime+""" and change to '20211212 07:00:00.000' then it works but I need a way for the script to keep track of the current date rather than going back and changing every day.

from datetime import timedelta
import numpy as np
from pandas import DataFrame 
import pandas as pd
import pymssql


from datetime import date, timedelta
today = date.today()
yesterday = today - timedelta(days = 1)

BEGINDATE=yesterday
ENDDATE=today
BEGINDATE=BEGINDATE.strftime("%Y%m%d")
ENDDATE=ENDDATE.strftime("%Y%m%d")
StartTime = BEGINDATE +' 06:00:00:000'
print(StartTime)

Query= """SET NOCOUNT ON
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = """+StartTime+"""
SET @EndDate = '20211213 07:00:00.000'
SET NOCOUNT OFF
SELECT temp.TagName ,DateTime ,Value ,vValue ,MinRaw = ISNULL(Cast(AnalogTag.MinRaw as VarChar(20)),'N/A') ,MaxRaw = ISNULL(Cast(AnalogTag.MaxRaw as VarChar(20)),'N/A') ,Unit = ISNULL(Cast(EngineeringUnit.Unit as VarChar(20)),'N/A') ,StartDateTime From (
    SELECT  * 
     FROM History
     WHERE History.TagName IN ('S03_FT03_04_TOT01')
     AND wwRetrievalMode = 'Cyclic'
     AND wwCycleCount = 1440
     AND wwVersion = 'Latest'
     AND DateTime >= @StartDate
     AND DateTime <= @EndDate) temp
LEFT JOIN AnalogTag ON AnalogTag.TagName =temp.TagName
LEFT JOIN EngineeringUnit ON AnalogTag.EUKey = EngineeringUnit.EUKey
 WHERE temp.StartDateTime >= @StartDate"""
cur.execute(Query)
print(Query)

@KlausD. Inside the Query? Is it not supposed to be left outside the quotes as it's a variable? — XYZ123
– XYZ123, Commented Dec 13, 2021 at 13:55
Print out the Query before executing it and compare the @StartDate and @EndDate lines. — Klaus D.
– Klaus D., Commented Dec 13, 2021 at 14:01

Parfait · Accepted Answer · 2021-12-13 16:43:30Z

Consider running parameterization in Python passed into a prepared SQL statement without declaring @ params. Also, keep all variables in datetimes without any string conversion.

Not quite sure what your date ranges require given 6 AM and 7 AM confusion, below calculates range from yesterday at 6 AM to today at 5:59:59 AM (for 24 hours). Adjust as needed. Finally, final WHERE is moved into subquery.

from datetime import datetime, date, timedelta
import numpy as np
from pandas import DataFrame 
import pandas as pd
import pymssql

BEGINDATE = datetime.combine(
    date.today() - timedelta(days = 1), datetime.min.time()
) + timedelta(hours = 6)
print(BEGINDATE)
# 2021-12-12 06:00:00

ENDDATE = BEGINDATE + timedelta(days = 1) - timedelta(seconds=1)
print(ENDDATE)
# 2021-12-13 05:59:59

### PREPARED STATEMENT WITH %s PLACEHOLDERS
Query= """SELECT temp.TagName 
                , [DateTime] 
                , [Value] 
                , vValue  
                , MinRaw = ISNULL(CAST(AnalogTag.MinRaw AS VARCHAR(20)), 'N/A')
                , MaxRaw = ISNULL(CAST(AnalogTag.MaxRaw AS VARCHAR(20)), 'N/A') 
                , Unit = ISNULL(CAST(EngineeringUnit.Unit AS VARCHAR(20)), 'N/A')
                , StartDateTime 
          FROM (
              SELECT  * 
              FROM History
              WHERE History.TagName IN ('S03_FT03_04_TOT01')
                AND wwRetrievalMode = 'Cyclic'
                AND wwCycleCount = 1440
                AND wwVersion = 'Latest'
                AND [DateTime] >= %s
                AND [DateTime] <= %s
                AND StartDateTime >= %s
          ) temp
          LEFT JOIN AnalogTag ON AnalogTag.TagName = temp.TagName
          LEFT JOIN EngineeringUnit ON AnalogTag.EUKey = EngineeringUnit.EUKey
       """

# RUN QUERY WITH PARAMETERS
cur.execute(Query, [BEGINDATE, ENDDATE, BEGINDATE])
print(Query)

Collectives™ on Stack Overflow

Getting a syntax error when trying to execute this SQL query in Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related