Data Range SQL Query

ndutra · May 23, 2018, 4:23pm

Using an example code snippet from the Ignition Manual I am using a DateRange component to filter table data based on time in an SQL query, as soon as I close the script window I get an error with the query. The Details are Incorrect Syntax, can anyone help out? I’m not sure what I’m doing wrong, FYI the column that is being filtered is based off the tag var LastChange. See Below…

MMaynard · May 23, 2018, 4:28pm

Put single quotes around the control values.

'{Root Container.Date Range.startDate}'

ndutra · May 23, 2018, 4:35pm

That seemed to work, I get no errors. But my data is gone now? The time string format is the same.

PGriffith · May 23, 2018, 4:40pm

There’s probably a difference in format syntax that is causing something unexpected. Try checking what the output looks like with something like:

SELECT '{Root Container.Date Range.startDate}'

Then insert the date formatted string into our original query to see whether it’s a date format the RDBMS expects (usually, DBs will only automatically cast strings -> dates in very specific formats)

MMaynard · May 23, 2018, 4:43pm

Also I see now that you are selecting on a timestamp field.
Is that an actual SQL Timestamp field or a datetime field you named that?

Actual timestamp fields aren’t designed to be used as datetime fields.

ndutra · May 23, 2018, 4:48pm

I am selected a column which is being populated by the tag value of LastChanged, see below… both I believe are string values in SQL: varchar(255)

ndutra · May 23, 2018, 4:59pm

I do have a formatting issue it will not allow me to insert the daterange value into the DB column, do you mean if I formatted the LastChange tag to match the DateRange or vise-versa this should work?

ndutra · May 23, 2018, 5:16pm

Worked! Thank you

MMaynard · May 23, 2018, 5:16pm

Since you have a space in the table name you will have to wrap that in quotes on your insert or brackets.

INSERT INTO [Backflow Preventer]
or
INSERT INTO 'Backflow Preventer'

ndutra · May 23, 2018, 5:18pm

I mistyped the table name, it should be Backflow_Calibration, it ended up being the format of times were mismatched I added the system.date.format to make them line up. Thank you for your time!