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

**URL:** <https://forum.inductiveautomation.com/t/report-scripting-system-db-runprepupdate-with-variable-date-to-sql-query/57642>\
**Category:** Ignition\
**Tags:** ignition79\
**Created:** [March 25, 2022, 2:02pm UTC](https://forum.inductiveautomation.com/t/report-scripting-system-db-runprepupdate-with-variable-date-to-sql-query/57642 "2022-03-25T14:02:33Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Richard\_Tryon](https://avatars.discourse-cdn.com/v4/letter/r/ac8455/32.png) [@Richard\_Tryon](https://forum.inductiveautomation.com/u/Richard_Tryon)\
**Post date:** [March 25, 2022, 2:02pm UTC](https://forum.inductiveautomation.com/t/report-scripting-system-db-runprepupdate-with-variable-date-to-sql-query/57642/1 "2022-03-25T14:02:33Z")

</div>

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])

```

‘’’

---

<div class="post-metadata">

**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\
**Post date:** [March 25, 2022, 2:08pm UTC](https://forum.inductiveautomation.com/t/report-scripting-system-db-runprepupdate-with-variable-date-to-sql-query/57642/2 "2022-03-25T14:08:58Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Richard\_Tryon](https://avatars.discourse-cdn.com/v4/letter/r/ac8455/32.png) [@Richard\_Tryon](https://forum.inductiveautomation.com/u/Richard_Tryon)\
**Post date:** [March 25, 2022, 2:46pm UTC](https://forum.inductiveautomation.com/t/report-scripting-system-db-runprepupdate-with-variable-date-to-sql-query/57642/3 "2022-03-25T14:46:05Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![Richard\_Tryon](https://avatars.discourse-cdn.com/v4/letter/r/ac8455/32.png) [@Richard\_Tryon](https://forum.inductiveautomation.com/u/Richard_Tryon)\
**Post date:** [March 25, 2022, 2:52pm UTC](https://forum.inductiveautomation.com/t/report-scripting-system-db-runprepupdate-with-variable-date-to-sql-query/57642/4 "2022-03-25T14:52:45Z")

</div>

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])

```

‘’’