0

Trying to have Python SQL statement use current date instead of manually updating the string with a fixed date. I used few examples that I commented out below that did not work. Is this possible to have current date in Python sql statement?

#Pull data from exat SQL Server into dataframe

with sscon.exat_connection() as conn:

    fields = "app.RecordID, app.IproAppealID, app.Received, app.CaseDue, pla.CompanyName, appi.AppealerAddress1,app.EnteredBy"

    table = "dbo.tblAppealInformation app LEFT OUTER JOIN dbo.tblPlanInformation pla ON (app.PlanID = pla.PlanID) LEFT OUTER JOIN dbo.tblAppealerInformation appi ON (app.AppealerID = appi.AppealerID)"

    conditions = "app.Received > '2022-06-14' AND app.ClientID = 117" #yyyy-mm-dd

    #conditions = "app.Received > {datetime.datetime.now():%Y-%m-%d%H:%M:%S} AND app.ClientID = 117" #yyyy-mm-dd

    #conditions = "app.Received > {now:%Y-%m-%d%H:%M:%S} AND app.ClientID = 117" #yyyy-mm-dd

    order = "3"

    sql = (f"SELECT {fields} "

           f"FROM {table} "

           f"WHERE {conditions} "

           f"ORDER BY {order}")

    # print(sql)
Nick
  • 138,499
  • 22
  • 57
  • 95
mekcox
  • 1
  • 2
  • Just use `CONVERT(date, GETDATE())` – Nick Jun 17 '22 at 02:37
  • I modify the conditions string statement of python calling SQL statement: conditions = "app.Received > (CONVERT(date, GETDATE()) AND app.ClientID = 117" #yyyy-mm-dd – mekcox Jun 17 '22 at 19:30
  • I got fata error on Spyder console: atabaseError: Execution failed on sql 'SELECT app.RecordID, app.IproAppealID, app.Received, app.CaseDue, pla.CompanyName, appi.AppealerAddress1,app.EnteredBy FROM dbo.tblAppealInformation app LEFT OUTER JOIN dbo.tblPlanInformation pla ON (app.PlanID = pla.PlanID) LEFT OUTER JOIN dbo.tblAppealerInformation appi ON (app.AppealerID = appi.AppealerID) WHERE app.Received > (CONVERT(date, GETDATE()) AND app.ClientID = 117 ORDER BY 3': ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'AND'. (156) (SQLExecDirectW)") – mekcox Jun 17 '22 at 19:32
  • Not sure what else I can do or is this not possible – mekcox Jun 17 '22 at 19:34
  • I got this to work without any fatal errors: CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 111))) ** Thanks Nick & everyone on Stackflow! – mekcox Jun 17 '22 at 19:50
  • Glad to hear you got it working. The problem with your SQL statement was that you had an extra `(` in front of `CONVERT`; it should have been just `SELECT app.RecordID, app.IproAppealID, app.Received, app.CaseDue, pla.CompanyName, appi.AppealerAddress1,app.EnteredBy FROM dbo.tblAppealInformation app LEFT OUTER JOIN dbo.tblPlanInformation pla ON (app.PlanID = pla.PlanID) LEFT OUTER JOIN dbo.tblAppealerInformation appi ON (app.AppealerID = appi.AppealerID) WHERE app.Received > CONVERT(date, GETDATE()) AND app.ClientID = 117 ORDER BY 3` – Nick Jun 18 '22 at 01:03
  • Note that `CONVERT(date, GETDATE())` should work... – Nick Jun 18 '22 at 01:03

0 Answers0