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?
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')
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.