Hi all,
I am attempting to run a MSSQL query via scripting for a perspective project. My error message is showing that the query is attempting to execute with the following date formatting: Mon Dec 27 15:03:34 PST 2021.
The date is a param within the perspective session. When I property bind the param to a label, it displays the date as 2021-12-27 15:03:34.
Any idea what is going on underneath that would be changing this? Let me know if you need any clarification.
Thanks.
We probably need to see your SQL query. Post the code (using the </> formatting button) rather than a screengrab. That way we can copy it into our answers and edit it.
How is the date being passed to the query?
I have run into this issue before as well. Creating a query via scripting that uses a datetime variable can be problematic because at some point the datetime needs to be converted into a string, since the entire query is of type string. Unfortunately, the date format property is not something that seems to carry over when casting a datetime variable to a string, and of course SQL does not like the format that the string version of the datetime defaults to (e.g. Mon Dec 27 15:03:34 PST 2021).
I’m sure there are some good scripting solutions to this issue, and hopefully another forum-dweller smarter than I can point one out. My solution to this issue has been to simply create the query as a named query and use a query binding that can reference the datetime as a query parameter wherever the query is needed.
Use a “Prep” query with ? substitution or a named query with datetime value parameters. The whole point of such is to avoid string conversion entirely. The milliseconds UTC that underlies Java datetime values are passed by JDBC directly in binary to the DB to avoid problems with locales and other string formatting conundrums. If you are messing with string formatting of date/time for your DB, you’ve already screwed up.
Hi all, this was definitely the issue. I had been converting the datetime to a string and before running it in the query. I used a named query to avoid this and I no longer encounter this issue.