Alleged date time conversion error, SQL Server to Ignition

[mdemaris]

I have a stored procedure that returns an integer, just a sum of numbers. The named query (scalar) calls this SP, sends a datetime to filter the results. Testing the named query, I get the correct integer for the current datetime.

I create a custom prop and bind to that query, and get a conversion error if I send a now() value. Error converting datetime2 to datetime. So I used dateFormat(now(), 'yyyy/MM/dd hh:m:ss'), same error.

The actual value of the datetime must be like this for SQL Server to accept:
'2024-07-31 10:54:01'. What could/should I do with now() to get what I need?

Thanks

[pturmel]

Check your JDBC driver version. datetime2 support was rather late, IIRC.

[mdemaris]

Is there a way to check that through the gateway?

[pturmel]

If you look in the user-lib/jdbc folder in the install directory, you should be able to tell from the file name of the jar.

[mdemaris]

had to remote in... ver. 11.2

How do I determine the Java version? Looks like the Java JRE is built into Ignition, and is maintained by the IA devs. So, I should be able to try the jre11 driver and see if that helps. If anything breaks, though... I'll copy the current jre driver, just in case.

[pturmel]

Simplest is to look at the gateway status page.

[mdemaris]

[mdemaris]

Well, updating the driver did not work.
This, is the query and passing now().

The error is the datetime2 conversion I mentioned earlier. The binding for this.custom.now is this: dateFormat(now(), 'yyyy/MM/dd hh:mm:ss').

The commented datetime in the Value field was a successful test.

Using the Scripting Tool, system.date.now() is a java.util.date object. If I print that out, I get: Wed Jul 31 15:34:05 PDT 2024 .
If I send that to the SAME query used in the binding, I get the expected integer returned!

I am missing something here in the expression language versus Jython. A simple cast, convert, format???

I found this thread:

Perspective date time input - Can you add an option for current datetime?

This seems to work: def runAction(self): date = system.date.now() self.props.value = system.date.setTime(system.date.now(),system.date.getHour24(date),system.date.getMinute(date),0)

Not sure how Transistor's code worked with the 0 in the now(0). I kept getting errors. So I adapted r123's code to fit my widget, removed the binding on this.custom.now and that seems to work.
I just don't understand why now() will not work in an expression binding for a named query datetime parameter, but system.date.now() will.

[Andy_Guias]

Have you tried using this for your binding:

system.date.format(system.date.now(), 'yyyy-MM-dd HH:mm:ss')

This usually does the trick for me.

[pturmel]

@mdemaris

The binding for this.custom.now is this: dateFormat(now(), 'yyyy/MM/dd hh:mm:ss').

Then you aren't passing a datetime object to the NQ, you are passing a string. Get rid of the dateFormat() entirely.

Don't convert dates to strings except for display to the user (and best to not do that either, without involving session time zones).

Strings that look like date/time values are NOT datetime objects.

Let me rephrase: Do NOT supply strings to NQ datetime parameters, nor to scripted prep queries question-mark parameters, that expect datetime objects.

[mdemaris]

As I mentioned earlier, passing this string to a NQ does not work, when the query is expecting a datetime value. And Phil stresses that emphatically in his following post,

[Andy_Guias]