MSSQL query between 2 dates

Ignition
, ,
1

I’m pretty new to Ignition, and I have seen other threads related to this topic, but have not found anything that resolves this issue.

I need to use the report viewer with a date range component to set the start and end dates for my sql query which is microsoft sequel. I have seen that I need to stick to using UTC time, but I do not have that option. I have passed the Start Date and End Date into Strings formatted to be accepted into SQL, but I get this message when I execute in the Database query browser:

“Conversion failed when converting date and/or time from character string.”

Is there a way to get this query to work with MSSQL without going to UTC time?

Thank you.

2

Why are you converting to string? Pass them as parameters.

Edit: You might be toast. I can’t find a connection property that signals UTC on a per-DB connection basis. ):

3

Well I certainly understand if I am SOL, but I’ve got to at least try to avoid to spin up a separate mySQL server and ask for another license. Here’s the detail of what I have done…

I can only use MSSQL and local time. Here's been my guess.

SELECT t_stamp,
       concentration,
       Temperature,
       MotorSts,
       valveSts
 FROM pryogenation
WHERE t_stamp BETWEEN '{StartString}'
 AND '{EndString}'

SELECT t_stamp,
       concentration,
       Temperature,
       MotorSts,
       valveSts
 FROM pryogenation
WHERE t_stamp BETWEEN {StartDate}
 AND {EndDate}

Default values...
StartDate - dateArithmetic(now(), -1, "day")
EndDate - now()
StartString - dateFormat({StartDate}, 'YYYY-MM-DD HH:MM:SS')
EndString - dateFormat({EndDate}, 'YYYY-MM-DD HH:MM:SS')

I have even tried to do StartString and EndString as follows...
dateFormat({StartDate}, 'YYYY-MM-DD') + 'T' + dateFormat({StartDate}, 'HH:MM:SS')
4

Any time you use curly braces in a query, Ignition is going to convert that item to a string to insert in the query. While sometimes unavoidable, it is prone to error (variation by locale and time zone for date/time presentation) and prone to hacking. Use a named query with parameters, so string conversion of values, especially timestamps, doesn’t happen. In scripts, you can use “Prep” queries to similarly avoid string conversion of values.

Fix that first.

1 Like