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

**URL:** <https://forum.inductiveautomation.com/t/mysql-simple-select-query-by-passing-in-startdate-and-enddate-report-builder/14815>\
**Category:** Ignition\
**Created:** [July 18, 2017, 12:41pm UTC](https://forum.inductiveautomation.com/t/mysql-simple-select-query-by-passing-in-startdate-and-enddate-report-builder/14815 "2017-07-18T12:41:54Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Srinivasa](https://avatars.discourse-cdn.com/v4/letter/s/bbce88/32.png) [@Srinivasa](https://forum.inductiveautomation.com/u/Srinivasa)\
**Post date:** [July 18, 2017, 12:41pm UTC](https://forum.inductiveautomation.com/t/mysql-simple-select-query-by-passing-in-startdate-and-enddate-report-builder/14815/1 "2017-07-18T12:41:54Z")

</div>

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")

---

<div class="post-metadata">

**Author:** ![KathyApplebaum](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kathyapplebaum/32/7261_2.png) [@KathyApplebaum](https://forum.inductiveautomation.com/u/KathyApplebaum)\
**Post date:** [July 18, 2017, 1:30pm UTC](https://forum.inductiveautomation.com/t/mysql-simple-select-query-by-passing-in-startdate-and-enddate-report-builder/14815/2 "2017-07-18T13:30:26Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![KathyApplebaum](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kathyapplebaum/32/7261_2.png) [@KathyApplebaum](https://forum.inductiveautomation.com/u/KathyApplebaum)\
**Post date:** [July 18, 2017, 2:35pm UTC](https://forum.inductiveautomation.com/t/mysql-simple-select-query-by-passing-in-startdate-and-enddate-report-builder/14815/3 "2017-07-18T14:35:35Z")

</div>

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}

---

<div class="post-metadata">

**Author:** ![Srinivasa](https://avatars.discourse-cdn.com/v4/letter/s/bbce88/32.png) [@Srinivasa](https://forum.inductiveautomation.com/u/Srinivasa)\
**Post date:** [July 18, 2017, 3:02pm UTC](https://forum.inductiveautomation.com/t/mysql-simple-select-query-by-passing-in-startdate-and-enddate-report-builder/14815/4 "2017-07-18T15:02:18Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Srinivasa](https://avatars.discourse-cdn.com/v4/letter/s/bbce88/32.png) [@Srinivasa](https://forum.inductiveautomation.com/u/Srinivasa)\
**Post date:** [July 18, 2017, 3:32pm UTC](https://forum.inductiveautomation.com/t/mysql-simple-select-query-by-passing-in-startdate-and-enddate-report-builder/14815/5 "2017-07-18T15:32:18Z")

</div>

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?

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/c/ce2422e7bbb27ca76f1f71a4e38280a57df08094.png)

---

<div class="post-metadata">

**Author:** ![Paul.Scott](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul.scott/32/4578_2.png) [@Paul.Scott](https://forum.inductiveautomation.com/u/Paul.Scott)\
**Post date:** [July 18, 2017, 3:59pm UTC](https://forum.inductiveautomation.com/t/mysql-simple-select-query-by-passing-in-startdate-and-enddate-report-builder/14815/6 "2017-07-18T15:59:01Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Srinivasa](https://avatars.discourse-cdn.com/v4/letter/s/bbce88/32.png) [@Srinivasa](https://forum.inductiveautomation.com/u/Srinivasa)\
**Post date:** [July 18, 2017, 4:02pm UTC](https://forum.inductiveautomation.com/t/mysql-simple-select-query-by-passing-in-startdate-and-enddate-report-builder/14815/7 "2017-07-18T16:02:01Z")

</div>

That’s it. Thank You

---

<div class="post-metadata">

**Author:** ![gcampbell](https://avatars.discourse-cdn.com/v4/letter/g/3ab097/32.png) [@gcampbell](https://forum.inductiveautomation.com/u/gcampbell)\
**Post date:** [October 5, 2017, 9:22pm UTC](https://forum.inductiveautomation.com/t/mysql-simple-select-query-by-passing-in-startdate-and-enddate-report-builder/14815/8 "2017-10-05T21:22:01Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![marcg](https://avatars.discourse-cdn.com/v4/letter/m/bb73d2/32.png) [@marcg](https://forum.inductiveautomation.com/u/marcg)\
**Post date:** [June 17, 2020, 3:59am UTC](https://forum.inductiveautomation.com/t/mysql-simple-select-query-by-passing-in-startdate-and-enddate-report-builder/14815/9 "2020-06-17T03:59:17Z")

</div>

> [@KathyApplebaum](#):
>
> where t\_stamp between ? and ?

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