# Query stopped working do to date time format syntax after upgrading from 7.9.8 to 7.9.11 **URL:** \ **Category:** Ignition\ **Tags:** ignition79\ **Created:** [June 8, 2019, 6:53pm UTC](https://forum.inductiveautomation.com/t/query-stopped-working-do-to-date-time-format-syntax-after-upgrading-from-7-9-8-to-7-9-11/25662 "2019-06-08T18:53:01Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![19tb21](https://avatars.discourse-cdn.com/v4/letter/1/0ea827/32.png) [@19tb21](https://forum.inductiveautomation.com/u/19tb21)\ **Post date:** [June 8, 2019, 6:53pm UTC](https://forum.inductiveautomation.com/t/query-stopped-working-do-to-date-time-format-syntax-after-upgrading-from-7-9-8-to-7-9-11/25662/1 "2019-06-08T18:53:01Z")
I have a simple query that worked previously, but after upgrading from 7.9.8 to 7.9.11 I am getting an Oracle error ORA-01830. Here is the query in question: `SELECT "t_stamp", "assm_parts" FROM DBH where "t_stamp" BETWEEN TO_DATE('{Root Container.Date Range.startDate}', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('{Root Container.Date Range.endDate}', 'YYYY-MM-DD HH24:MI:SS')` Oracle date syntax is a pain, but I didn’t think updating Ignition would have any effect. And it worked previously. Here it the full error: ```auto SQLQuery(query=SELECT "t_stamp", "assm_parts" FROM DBH where "t_stamp" BETWEEN TO_DATE('2019-06-08 02:00:00.000', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2019-06-09 02:00:00.000', 'YYYY-MM-DD HH24:MI:SS'), database=ignition_scada_test)@0ms On: metrics.Root Container.Chart.Data caused by GatewayException: ORA-01830: date format picture ends before converting entire input string caused by SQLDataException: ORA-01830: date format picture ends before converting entire input string ``` ---
**Author:** ![19tb21](https://avatars.discourse-cdn.com/v4/letter/1/0ea827/32.png) [@19tb21](https://forum.inductiveautomation.com/u/19tb21)\ **Post date:** [June 8, 2019, 6:59pm UTC](https://forum.inductiveautomation.com/t/query-stopped-working-do-to-date-time-format-syntax-after-upgrading-from-7-9-8-to-7-9-11/25662/2 "2019-06-08T18:59:59Z")
Oh it looks like perhaps going from 7.9.8 added millisecond precision to my startDate and endDate properties. Does anyone know a good way to change the precision on the startDate and endDate from the Date Range object? ---
**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\ **Post date:** [June 8, 2019, 7:45pm UTC](https://forum.inductiveautomation.com/t/query-stopped-working-do-to-date-time-format-syntax-after-upgrading-from-7-9-8-to-7-9-11/25662/3 "2019-06-08T19:45:12Z")
You could create a hidden label or text box controls and bind them to an expression of `dateFormat({Root Container.Date Range.startDate},'yyyy-MM-dd HH:mm:ss')` Then pass those values to your query? I don’t deal with Oracle dates so I am not sure if that will work or not. ---
**Author:** ![19tb21](https://avatars.discourse-cdn.com/v4/letter/1/0ea827/32.png) [@19tb21](https://forum.inductiveautomation.com/u/19tb21)\ **Post date:** [June 8, 2019, 7:46pm UTC](https://forum.inductiveautomation.com/t/query-stopped-working-do-to-date-time-format-syntax-after-upgrading-from-7-9-8-to-7-9-11/25662/4 "2019-06-08T19:46:05Z")
yeah I am trying that, for whatever reason java is passing a fractional second and I can’t get the syntax right on the Oracle side to accept the fractional second ---
**Author:** ![19tb21](https://avatars.discourse-cdn.com/v4/letter/1/0ea827/32.png) [@19tb21](https://forum.inductiveautomation.com/u/19tb21)\ **Post date:** [June 8, 2019, 8:07pm UTC](https://forum.inductiveautomation.com/t/query-stopped-working-do-to-date-time-format-syntax-after-upgrading-from-7-9-8-to-7-9-11/25662/5 "2019-06-08T20:07:13Z")
Ok I finally got it to work. In case anyone running Oracle DB runs in to this, you must convert TO\_TIMESTAMP in your query and not TO\_DATE, because TO\_DATE does not support fractional seconds. Working query (I have gone back to just calling the date range property, not a custom property): `SELECT "t_stamp", "assm_parts" FROM DBH where "t_stamp" BETWEEN TO_TIMESTAMP('{Root Container.Date Range.startDate}','YYYY-MM-DD HH24:MI:SS.FF3') AND TO_TIMESTAMP('{Root Container.endDate}','YYYY-MM-DD HH24:MI:SS.FF3')` Can anyone confirm that between 7.9.8 and 7.9.11 the precision changed on the Java side? ---
**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:** [June 9, 2019, 2:19am UTC](https://forum.inductiveautomation.com/t/query-stopped-working-do-to-date-time-format-syntax-after-upgrading-from-7-9-8-to-7-9-11/25662/6 "2019-06-09T02:19:12Z")
You should convert to named queries and let JDBC handle these conversions (via named parameters) for you. Milliseconds on Date() objects is handled appropriately in pretty much all cases. ---
**Author:** ![mickificki](https://avatars.discourse-cdn.com/v4/letter/m/b9e5f3/32.png) [@mickificki](https://forum.inductiveautomation.com/u/mickificki)\ **Post date:** [September 18, 2019, 9:32pm UTC](https://forum.inductiveautomation.com/t/query-stopped-working-do-to-date-time-format-syntax-after-upgrading-from-7-9-8-to-7-9-11/25662/7 "2019-09-18T21:32:35Z")
Thank you for this. We had the exact situation and to\_timestamp helped!