MySQL Simple Select Query by passing in StartDate and EndDate Report Builder

Ignition
1

StartDate Parameter Data Type Date, its expression dateArithmetic(now(), -1, “day”)
EndDate Parameter Data Type Date, its expression dateArithmetic(now(), -1, “day”)

NONE of the following three would produce working report.

(1)

SELECT tghistory.*
FROM tghistory
where t_stamp between {StartDate} and {EndDate}

WARN: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘Jul 18 05:28:26 CDT 2017 and t_stamp <= Tue Jul 18 05:28:26 CDT 2017’ at line 3

(2)

SELECT tghistory.*
FROM tghistory
where t_stamp >= {StartDate} and t_stamp <= {EndDate}

WARN: Simple Query could not be completed
You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near ‘Jul 17 07:13:29 CDT 2017 and t_stamp <= Tue Jul 18 07:13:29 CDT 2017’ at line 3

(3)

SELECT tghistory.*
FROM tghistory
where t_stamp between concat(dateFormat({StartDate},“yyyy-MM-dd”), " 0:0:0") and concat(dateFormat({EndDate},“yyyy-MM-dd”), " 23:59:59")

1 Like
2

Have you tried it without both the start and end date being set to the same expression, that is dateArithmetic(now(), -1, "day") ?

If end date is defined ahead of start date then it would be evaluated before start date, which means even though the times look the same there’s a few milliseconds difference. That just about guarantees a query won’t work.

3

Also, you’ll have better luck with (1) if you use a SQL Query (not a Basic SQL Query) and change the query to SELECT tghistory.*FROM tghistorywhere t_stamp between ? and ?

In the parameter boxes that show up at the bottom of the screen, put {StartDate} and {EndDate}

5 Likes
4

Hi Kathy,
It was my copy/paste mistake but EndDate parameter was set to now()

5

That worked well except I have to format the string to display data for today as below:
concat(dateFormat({StartDate},“yyyy-MM-dd”), " 0:0:0")
concat(dateFormat({EndDate},“yyyy-MM-dd”), " 23:59:59")

Can you tell me why the report is displaying to the right side of the page, as shown below, rather than display on the next page?

image.png1165×803 178 KB

6

The table object (not the rows in the table) has a Column Count property. Try setting that back to 1.

7

That’s it. Thank You

8

First off I hope I can explain this well enough for someone to understand. I apologize ahead of time.
I am having issues along the same line as this thread. I am trying to run a query to capture minute data for a daily report that needs to run automatically. The report is pulling from a database that has 20 different UNIT_ID’s and I only need the ID 2154. I need to sort by time stamp but, I need to run this report daily.
However using this code:
SELECT CEMS_DATA_MIN.DATE_TIME,
CEMS_DATA_MIN.UNIT_ID,
CEMS_DATA_MIN.DATA32,
CEMS_DATA_MIN.DATA30
FROM CEMS_DATA_MIN
WHERE CEMS_DATA_MIN.UNIT_ID = 2154
ORDER BY CEMS_DATA_MIN.DATE_TIME

Both the Preview and the DB query browser run fine. However, the DB has been gathering minute data for over 5 years now so it is rather large. When I run this as a report it times out. I figure it is because the report is pulling from all 5 years worth of data. I have the schedule configured to run between the StartDate and the EndDate and I have it configured to run once daily at 11:59 PM but, this does not help either. I tried the example below but when I run it I get "Error running query: Select… ORA-01008: not all variables bound.
SELECT CEMS_DATA_MIN.DATE_TIME,
CEMS_DATA_MIN.UNIT_ID,
CEMS_DATA_MIN.DATA32,
CEMS_DATA_MIN.DATA30
FROM CEMS_DATA_MIN
WHERE CEMS_DATA_MIN.DATE_TIME BETWEEN ? AND ? AND CEMS_DATA_MIN.UNIT_ID = 2154
ORDER BY CEMS_DATA_MIN.DATE_TIME

Parameter 1 {StartDate} and Parameter 2 {EndDate}
I have tried many different variations on the above code but, I always get an error of one kind or another. Any help on this would be greatly appreciated.

9

Hi! this solved myproblem! thank you very much madam!

1 Like