How things work - passing Date value via query string parameter gotcha

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 :slight_smile:
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 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). resulted in "Tue Jul 04 09:39:58 BST 2023" The named query did not work
3 i formatted output using 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 did work but when it was not formatted it did not work? i understand that 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 - the query did not work as well - i am not sure why -database column is using datetime parameter so it should work :slight_smile: - pls see second screenshot

disclaimer: I'm only speculating here

  1. Why use query strings for this ? You're passing a date, that's a value. Use the date type and you'll be fine
  2. 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.
  3. 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.


that info gets me sorted - thanks again