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

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

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.

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}

3 Likes

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

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?

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

That’s it. Thank You

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.

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

1 Like