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

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 Servern”).

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)

Answer

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)