hi
I am trying to improve my understanding of time formats when using named queries. It tripped me a few times in the past so enough is enough 
For example:
1 in named query i try to pass current timestamp parameter so i created querystring parameter to hold that value.
2 when testing named query i used system.date.now() in script console to have an example of the date which i passed as a parameter to text (i added single quotes as the date has to be passed as a string). System.time.now() resulted in "Tue Jul 04 09:39:58 BST 2023" The named query did not work
3 i formatted system.time.now output using system.date.format function which printed as "2023-07-04 09:39:58" When passed to the named query it did work.
I read somewhere that using curly braces (which are used when passing querystring parameter in the named query) somehow breaks the date.
Could anybody please share their understanding of:
1 are there any good practices when passing dates via Named Query?
2 why passing formatted output of the system.date.now did work but when it was not formatted it did not work? i understand that system.date.format produces string and this string system converted to date which was possible when using formatted date and impossible when using raw output - is it right?
3 when i changed parameter from querystring to value and selected data type and pasted the output of system.date.now - the query did not work as well - i am not sure why -database column is using datetime parameter so it should work - pls see second screenshot
disclaimer: I'm only speculating here
- Why use query strings for this ? You're passing a date, that's a value. Use the
date type and you'll be fine
- If you're passing a string instead of a date, the database engine can handle conversion. But not for any format. The error in your first screenshot makes it clear: It didn't recognize the format, so the conversion failed.
- After you formatted the date to a particular format that it recognizes, the conversion worked, and the query ran succesfully.
1 Like
I have 2 parameters for report i.e startdate and enddate and set the data type to string and have a default datetime value as string, and I had bind this 2 values from a namedquery where i am getting date time in yyyy-MM-dd HH:mm:ss format.
Then I used this 2 parameters to run a basic query to get datas for the report.
When I set the datatype of startdate and endate as date and pass date time value I get error.
1 Like
very sound logic - thanks
i suggest the following:
1 select parameter type as "Query String"
2 test your named query in ignition -paste the formatted date string wrapped in single quotes eg.
'2023-07-04 09:39:58 as one of parameteres
Well... What's the error ?
Query string parameters are STRINGS. Converting a datetime type to string usually loses information, and then requires parsing to recover.
FWIW, if you are using any querystring parameters in a named query, you are defeating the security advantages using value parameters provides. Under no circumstances should user-generated values ever be allowed in a querystring parameter.
Pass your datetime values as value parameters. Full stop.
If you have dates/times already in strings, convert them to datetime values using a system function with an explicit format conversion, then pass the resulting datetime objects as value parameters.
3 Likes
that info gets me sorted - thanks again
