# Passing variables into SQL query

**URL:** <https://forum.inductiveautomation.com/t/passing-variables-into-sql-query/76226>\
**Category:** Ignition\
**Created:** [July 11, 2023, 8:22pm UTC](https://forum.inductiveautomation.com/t/passing-variables-into-sql-query/76226 "2023-07-11T20:22:08Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![209prc](https://avatars.discourse-cdn.com/v4/letter/2/838e76/32.png) [@209prc](https://forum.inductiveautomation.com/u/209prc)\
**Post date:** [July 11, 2023, 8:22pm UTC](https://forum.inductiveautomation.com/t/passing-variables-into-sql-query/76226/1 "2023-07-11T20:22:08Z")

</div>

I have what would seem to be a simple script that collects a date from popup calendar name 'weekSelect', places the value into a variable named weekStart and then formats the variable with dateFormat. What I'm having an issue with is getting the variable to work with the same scripts SQL query. The error I receive, GatewayException & SQLServerException: Conversion failed when converting date and/or time from character string.

Code (simplified as to not show all tables):  
import datetime

reportDate = datetime.datetime.now().strftime("%Y-%m-%d") # Convert to string format  
weekStart = event.source.parent.getComponent('weekSelect').date  
weekEnd = system.date.addDays(weekStart, 7)

####### Convert weekStart and weekEnd to SQL-compatible format  
weekStart = system.db.dateFormat(weekStart, "yyyy-MM-dd")  
weekEnd = system.db.dateFormat(weekEnd, "yyyy-MM-dd")

query = """  
DECLARE @weekStart DATE = 'weekStart';  
DECLARE @weekEnd DATE = 'weekEnd';

SELECT  
\*  
FROM  
timesheets t  
JOIN projects p ON t.projectId = p.id  
JOIN users u ON t.userID = u.id  
WHERE  
t.startTime \>= @weekStart AND t.startTime \< @weekEnd  
ORDER BY  
t.startTime  
"""

result = system.db.runQuery(query).

Seems I'm having an issue getting the variable called from the query. Several attempts in different formats have failed.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [July 11, 2023, 8:36pm UTC](https://forum.inductiveautomation.com/t/passing-variables-into-sql-query/76226/2 "2023-07-11T20:36:18Z")

</div>

A few things:

1.) It's really hard to decipher what exactly is happening if the code isn't formatted correctly, if you use the preformatted text option (`</>` button) it will retain all of your codes formatting and make it much more readable.

2.) You should at a minimum be using `system.db.runPrepQuery()` but really you should create a named query and use `system.db.runNamedQuery()`

3.) Don't format the dates and then try to concatenate them into the query string (doing this particuarly with user provided data is a very poor practice). Instead, use one of the suggested functions above and provide the date object unchanged and let the JDBC driver hand it off to the DB.

4.) The code doesn't work because you are setting your parameters to the string literals 'weekStart', and 'weekEnd', which aren't valid dates and can not be parsed into dates by your DB. I'm sure if you look at the error you are getting you would see something along those lines.

Here is a link to the documentation for `system.db.runNamedQuery()`, post back if you need more help after trying this.

[https://docs.inductiveautomation.com/display/DOC81/system.db.runNamedQuery](https://docs.inductiveautomation.com/display/DOC81/system.db.runNamedQuery)