Report Scripting system.db.runPrepUpdate with variable date to SQL Query

Ignition
1

I am struggling with using variable dates (based upon 1 week span from today-1 week ago).
I tried the date format as both string and a value but no luck. If I enter in static date/time instead of using the ?, it works fine.

I get an error in the report preview regarding updateDataNameError global name ‘StartTime’ is not defined. In all the examples I’ve looked at, it seems like I have defined them simply by how they are used. How can I pass the dates into system.db.runPrepUpdate so it’s happy with it?

‘’’

EndDate = system.date.now()
#Subtract 7 days to examine the past week
StartDate = system.date.addDays(EndDate, -7)
#Format the given date as a string, so it compiles into the query string below
EndDate = system.date.format(EndDate, "yyyy-MM-dd HH:mm:ss")
StartDate = system.date.format(StartDate, "yyyy-MM-dd HH:mm:ss")

insQuery1 = """
INSERT INTO tbl_MMS_Excludetemp (FAULTEDID,LINENAME,DEVICENAME,OCCURRENCEDATE,CANCELLATIONDATE,PLCADDRESS,FACTOR,workSecs,shiftCode,KIBAN,Exclude)
SELECT a.FAULTEDID,a.LINENAME,a.DEVICENAME,a.OCCURRENCEDATE,a.CANCELLATIONDATE,a.PLCADDRESS,a.FACTOR,a.workSecs,a.shiftCode,a.KIBAN,a.Exclude
FROM tbl_MMS_MTTR_FACTOR b INNER JOIN tbl_MMS_Exclude a 
ON  a.Exclude = b.Exclude 
Where b.OCCURRENCEDATE> ? and b.OCCURRENCEDATE< ? and a.DEVICENAME = b.DEVICENAME and a.PLCADDRESS = b.PLCADDRESS
;
"""

system.db.runPrepUpdate(insQuery1, [EndTime, StartTime])

‘’’

2

From looking at your code it appears you just have EndTime and StartTime in the wrong order?
the ? marks and the values passed to them have to line up.

1 Like
3

It’s worse than that, I was staring at this so long, I replaced the text ‘Date’ with ‘Time’
so instead of using the variable EndDate I later typed EndTime.
'system.db.runPrepUpdate(insQuery1, [EndTime, StartTime])

Sorry for this post!

1 Like
4

Thanks again. Here is the correct script, working great now that I am using the same variable names throughout!

‘’’

EndDate = system.date.now()
#Subtract 7 days to examine the past week
StartDate = system.date.addDays(EndDate, -7)
#Format the given date as a string, so it compiles into the query string below
EndDate = system.date.format(EndDate, "yyyy-MM-dd HH:mm:ss")
StartDate = system.date.format(StartDate, "yyyy-MM-dd HH:mm:ss")

insQuery1 = """
INSERT INTO tbl_MMS_Excludetemp (FAULTEDID,LINENAME,DEVICENAME,OCCURRENCEDATE,CANCELLATIONDATE,PLCADDRESS,FACTOR,workSecs,shiftCode,KIBAN,Exclude)
SELECT a.FAULTEDID,a.LINENAME,a.DEVICENAME,a.OCCURRENCEDATE,a.CANCELLATIONDATE,a.PLCADDRESS,a.FACTOR,a.workSecs,a.shiftCode,a.KIBAN,a.Exclude
FROM tbl_MMS_MTTR_FACTOR b INNER JOIN tbl_MMS_Exclude a 
ON  a.Exclude = b.Exclude 
Where b.OCCURRENCEDATE> ? and b.OCCURRENCEDATE< ? and a.DEVICENAME = b.DEVICENAME and a.PLCADDRESS = b.PLCADDRESS
;
"""

system.db.runPrepUpdate(insQuery1, [StartDate, EndDate])

‘’’

1 Like