Hello.
Im searching for help to run a script to sql database,
i´m using 2 date time inputs as a range of time to search in sql
but i get conversion error.
some of you had an idea how to fix?
You are just quoting the path to the component properties. That passes the path to the database. To make those paths substitute the formatted dates you are trying to get, include curly braces, inside the quotes. Curly braces in SQL bindings and expressions is what triggers dynamic lookup.
Even better would be to use a named query, with actual datetime value parameters. That helps prevent localization problems and preserves precision.
2 Likes
thank you @pturmel , i tried several ways in the query, with curlybraces , quotes, Convert to smalltime, but didn´t work, im searching manual to do it with edit custom method, or some kind of scripting.
But thanks 4 the answer as i am new in ignition support is always good.
Actually, I just noticed that this is SQL for a tag. You won’t be able to use input from any gui components (neither Perspective nor Vision) in a gateway tag’s SQL or expression. Gateway tags are global, not specific to a client session. If you do intend for the result of the query to be used globally, I recommend bidirectionally binding your data/time input components to memory tags. Then you can use the memory tags in your query tag. If your query result is to be used just by the one client, then use a session or page custom property with a named query binding. Tie its parameters to your input components.
1 Like
Fernando,
The error message “Conversion failed when converting date and/or time from character string.” is coming from your SQL server.
The date formats accepted may vary slightly depending on what type of SQL server you are using (Microsoft SQL, Postgres, MySQL etc…) so I suggest that if this does not help, you search for help specific to your SQL server.
There are some suggestions here: sql - Conversion failed when converting date and/or time from character string while inserting datetime - Stack Overflow
It looks like you need to use ISO-8601 formatting, e.g. YYYY-MM-DDTHH:MM:SS, not “Mon Dec 27 15:25:47…”
You should be able to set the format in your date time input props, or your date/time tag properties.