Query stopped working do to date time format syntax after upgrading from 7.9.8 to 7.9.11

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:

SQLQuery(query=SELECT "t_stamp", "assm_parts"
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

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?

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.

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

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?

1 Like

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.


Thank you for this. We had the exact situation and to_timestamp helped!