Hi, I'm currently trying to create a basic SQL query on reports using a MYSQL Database.
SELECT * FROM group_table
WHERE t_stamp < '{EndDate}' ;
I've seen people commenting over this example on the forums but I haven't been able to solve it in my personal case.
When I tried the script above, Incorrect DATETIME value on '{EndDate}' appears.
If I take off the ' ' , SQL syntax error appears.
Currently I'm using transaction groups t_stamp, created by ignition and the Default {StartDate} and {EndDate} provided by ignition report also. This on ignition 8.1.
When I solve this, im planning to change it to this next script:
SELECT * FROM group_table
WHERE t_stamp between '{StartDate}' AND '{EndDate}' ;
But first I need to make the date parameters work
Is this a named query?
What datatype is EndDate
? (A datetime or a string?)
Hi, no is a Basic SQL query on Data Source Reports section. I'm calling the t_stamp value from the one that is created in a transaction group Database
I've tried both date and string type. By default report creates it as a date type format. Ive pasted an example except, i'm using ignition 8.1
Even this example is on the ignition page, if I do it dicrectly on MYSQL benchwork it does work, but on the report it doesn´t
I would suggest not using < and > with strings.
The BETWEEN example should work fine with the basic query, but the < operator likes to compare with a number not a string/datetime.
In the database, the datetime is stored as a number, so it's likely that it is trying to compare a number to be less than a date, as no conversions are specified.