# Alleged date time conversion error, SQL Server to Ignition **URL:** \ **Category:** Ignition\ **Created:** [July 31, 2024, 5:55pm UTC](https://forum.inductiveautomation.com/t/alleged-date-time-conversion-error-sql-server-to-ignition/93995 "2024-07-31T17:55:23Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\ **Post date:** [July 31, 2024, 5:55pm UTC](https://forum.inductiveautomation.com/t/alleged-date-time-conversion-error-sql-server-to-ignition/93995/1 "2024-07-31T17:55:23Z")
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 ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [July 31, 2024, 6:26pm UTC](https://forum.inductiveautomation.com/t/alleged-date-time-conversion-error-sql-server-to-ignition/93995/2 "2024-07-31T18:26:42Z")
Check your JDBC driver version. `datetime2` support was rather late, IIRC. ---
**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\ **Post date:** [July 31, 2024, 8:10pm UTC](https://forum.inductiveautomation.com/t/alleged-date-time-conversion-error-sql-server-to-ignition/93995/3 "2024-07-31T20:10:01Z")
Is there a way to check that through the gateway? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [July 31, 2024, 8:11pm UTC](https://forum.inductiveautomation.com/t/alleged-date-time-conversion-error-sql-server-to-ignition/93995/4 "2024-07-31T20:11:49Z")
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. ---
**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\ **Post date:** [July 31, 2024, 8:14pm UTC](https://forum.inductiveautomation.com/t/alleged-date-time-conversion-error-sql-server-to-ignition/93995/5 "2024-07-31T20:14:13Z")
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. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [July 31, 2024, 8:21pm UTC](https://forum.inductiveautomation.com/t/alleged-date-time-conversion-error-sql-server-to-ignition/93995/6 "2024-07-31T20:21:08Z")
Simplest is to look at the gateway status page. ---
**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\ **Post date:** [July 31, 2024, 8:24pm UTC](https://forum.inductiveautomation.com/t/alleged-date-time-conversion-error-sql-server-to-ignition/93995/7 "2024-07-31T20:24:07Z")
🤦‍♂️ ---
**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\ **Post date:** [July 31, 2024, 10:47pm UTC](https://forum.inductiveautomation.com/t/alleged-date-time-conversion-error-sql-server-to-ignition/93995/8 "2024-07-31T22:47:51Z")
Well, updating the driver did not work. This, is the query and passing `now()`. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/f/2f951f162fd582bb7a4cd40d2c90027617d609c1.png) 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?](https://forum.inductiveautomation.com/t/perspective-date-time-input-can-you-add-an-option-for-current-datetime/75415/3): > > 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. ---
**Author:** ![Andy\_Guias](https://avatars.discourse-cdn.com/v4/letter/a/b3f665/32.png) [@Andy\_Guias](https://forum.inductiveautomation.com/u/Andy_Guias)\ **Post date:** [July 31, 2024, 11:08pm UTC](https://forum.inductiveautomation.com/t/alleged-date-time-conversion-error-sql-server-to-ignition/93995/9 "2024-07-31T23:08:23Z")
Have you tried using this for your binding: ```python system.date.format(system.date.now(), 'yyyy-MM-dd HH:mm:ss') ``` This usually does the trick for me. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [July 31, 2024, 11:14pm UTC](https://forum.inductiveautomation.com/t/alleged-date-time-conversion-error-sql-server-to-ignition/93995/10 "2024-07-31T23:14:21Z")
> [@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** _. ---
**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\ **Post date:** [August 1, 2024, 12:09am UTC](https://forum.inductiveautomation.com/t/alleged-date-time-conversion-error-sql-server-to-ignition/93995/11 "2024-08-01T00:09:44Z")
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, 🙂 ---
**Author:** ![Andy\_Guias](https://avatars.discourse-cdn.com/v4/letter/a/b3f665/32.png) [@Andy\_Guias](https://forum.inductiveautomation.com/u/Andy_Guias)\ **Post date:** [August 1, 2024, 12:32am UTC](https://forum.inductiveautomation.com/t/alleged-date-time-conversion-error-sql-server-to-ignition/93995/12 "2024-08-01T00:32:02Z")
![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/9/893aa52e3df0610b3d59464908d36a6997641af5.png)