Data Range SQL Query

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…

Put single quotes around the control values.

‘{Root Container.Date Range.startDate}’

1 Like

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

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)

1 Like

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.

1 Like

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)

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?

Worked! Thank you

1 Like

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]
INSERT INTO ‘Backflow Preventer’

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