Ignition start/end Date property formatting using TO_STRING Oracle

Ignition
1

Has anyone had any experience formatting startDate and endDate properties from the native Ignition Date Range slider properly to use the TO_TIMESTAMP function in an Oracle DB?

SELECT "t_stamp" as "Date/Time", SN as "Serial", MP_sequence as "MP" 
	FROM RSER_FAIL_PRESS
		where "t_stamp" BETWEEN
			TO_TIMESTAMP('{Root Container.Date Range.startDate}', 'DD-Mon-RR HH24:MI:SS.FF')
			AND TO_TIMESTAMP('{Root Container.Date Range.endDate}', 'DD-Mon-RR HH24:MI:SS.FF')

I get ORA-01843: not a valid month

2

You’ll probably have to use intermediate string custom properties with your timestamps formatted for Oracle. I’ve had to do this with other DB brands at various time. But the correct solution is to convert to a named query and pass those as parameters – JDBC should take care of the rest.

3

I figured it out. The Power Table confused me by auto-formatting the Date/Time column. It was straight forward when I looked at the Query Browser output.

Here is how I got it to work:

SELECT "t_stamp" as "Date/Time", SN as "Serial", MP_sequence as "MP" 
	FROM RSER_FAIL_PRESS
		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')