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)

• Klaus D.: You are missing quotation marks around your StartTime.
• XYZ123: @KlausD. Inside the Query? Is it not supposed to be left outside the quotes as it's a variable?
• Klaus D.: Print out the Query before executing it and compare the @StartDate and @EndDate lines.
• XYZ123: Ah gotcha, it works now. Thanks

Answer 1

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)