SQL query 'WHERE' clause based on timestamp

Ignition
1

Hi,
I have a report query to MYSQL DB 8.0, similar to :
Select * from my_table where t_stamp between {StartDate} and {EndDate};

The {StartDate} and {EndDate} are report parameters of the DATE type
.
On the window where I use this report I have two Popup Calendar components.
I bind the report parameters to Popup Calendar.Date properties.

my_table.t_stamp is MYSQL DateTime type “yyyy-dd-mm hh:mm:ss”.

What is the proper syntax to be in query for the following portion:
where t_stamp between {StartDate} and {EndDate} ?

Thank you for any response.

2

Don’t use curly brace substitutions. They need quotes in the SQL because they are really string conversions. Use ‘?’ substitutions instead – those pass actual date objects to the JDBC driver.

2 Likes
3

Query problem.pdf (208.1 KB)
Thank you for reply. I appreciate it.
I got rid of the Error with your advice but I didn’t reach the goal.
Could you look at the attachment. I provide snapshots for two tests. First time I use hard typed dates and the data is returned. The second time I try to use ‘StartDate’ and ‘EndDate’. The return is empty. What am I doing wrong?

4

Put literal question marks, without quotes, where you are trying to put your start and end dates. Put your start and end date parameters in the expression boxes that appear for the question marks.