Datetime query for data source in Reports

Ignition
1

Hello all, just wonder if anyone can help with something basic in reports:

I have 4 parameters with expression:

rawstartdate
Date type
dateArithmetic(now(), -12, “hr”)

rawenddate
Date type
now()

StartDate
String type
dateFormat({rawstartdate}, “MM-dd-yyyy HH:mm:ss”)

EndDate
String type
dateFormat({rawenddate}, “MM-dd-yyyy HH:mm:ss”)

and my query is a Basic SQL Query:

SELECT *
FROM my_table
WHERE t_stamp BETWEEN {StartDate} AND {EndDate}
Order by t_stamp ASC

how ever, I keep getting syntax errors go to Design/Preview tab:
WARN: Simple Query could not be completedIncorrect syntax near ‘05’.
(current time is 5)

when I do the above query in the query browser, I do get the results.

Am I not type casting correctly in the parameters declarations?

Thanks.

2

I think the issue is with the date format you are doing, but why do you have 4 parameters instead of just 2?

3

A Basic SQL Query inserts parameters between curly braces using string conversions. Which means to appear properly in the query, non-numeric items must have single quotes outside the curly braces. Plus, java date/time values convert using java syntax, which is generally not parseable by DBs as dates.

The simplest solution is to use a Named Query, which can pass complex types (like dates) through to the DB without converting to/from strings.

1 Like