# Report Help Basic SQL Query/SQL Query **URL:** \ **Category:** Ignition\ **Created:** [May 16, 2017, 6:32pm UTC](https://forum.inductiveautomation.com/t/report-help-basic-sql-query-sql-query/14314 "2017-05-16T18:32:51Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![enault](https://avatars.discourse-cdn.com/v4/letter/e/b5ac83/32.png) [@enault](https://forum.inductiveautomation.com/u/enault)\ **Post date:** [May 16, 2017, 6:32pm UTC](https://forum.inductiveautomation.com/t/report-help-basic-sql-query-sql-query/14314/1 "2017-05-16T18:32:51Z")
Hi Im trying to build a report to get the value form a selectable Column Between StartDate and End Date Im using Ignition 7.9 My Database is a MariaDB My Table look like this MyTable\_ndx INT ColumnName1 FLOAT ColumnName2 FLOAT ColumnName3 FLOAT t\_stamp DATETIME Since i have way more that 3 column Id like to have a single query that use a parameter instead of the Column Name. The StartDate and EndDate need to be parameter too. i’ve try both SQL and Basic SQL and im having an issue with Both. The report using the Basic SQL Query seem to work fine in the report preview but when i run it in a Report Viewer in a Window in Preview Mode the report remain empty im pretty sure it has to do with the date formating SELECT MyTable\_ndx as ‘Index’, t\_stamp as ‘Time’, {ColumnNameParameter} as ‘ColumnName’ FROM MyTable WHERE (t\_stamp BETWEEN ‘{StartDate}’ AND ‘{EndDate}’) i have bind start and end date in the report viewer using the binding type Expression to something like this dateFormat({StartDateTag}, “yyyy-MM-dd HH:mm:ss”) but my report still come out empty showing the date formated like this “Apr 3,2017”. There really seem to be an issue whit the date formating in the report viewer I’ve also try The SQL Query and it seem to work properly but since i cant bind a parameter to a column name i would have to build a report for each Column which im trying to avoid. And while the report viewer show the correct value from the database the startdate and Enddate text field still show the date formated like “Apr 3,2017”. My SQL Query is SELECT MyTable\_ndx as ‘Index’, t\_stamp as ‘Time’, ColumnName1 as ‘ColumnName’ FROM MyTable WHERE (t\_stamp BETWEEN ? AND ?) With Parameter 1 and 2 being {StartDate} and {EndDate} So my main question would be Why is the report viewer not using the date formated with the format i’ve bind to it? Do you have any advice that would avoid me to create a report for every single column of my table? Thanks ---
**Author:** ![adam.morales](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/adam.morales/32/3405_2.png) [@adam.morales](https://forum.inductiveautomation.com/u/adam.morales)\ **Post date:** [May 18, 2017, 11:45pm UTC](https://forum.inductiveautomation.com/t/report-help-basic-sql-query-sql-query/14314/2 "2017-05-18T23:45:58Z")
A Basic SQL Query will allow you to parametize column names using syntax like this: `SELECT {ParameterReference} FROM MyTable` You will also need to use the dateFormat function of the expression language so that the StartDate and EndDate parameters are inserted into the query using the correct syntax for that database. This will look something like this: `dateFormat({StartDate}, “MM-dd-yyyy HH:mm:ss”)` You will have to define these formatted dates as separate parameters below the existing StartDate and EndDate parameters passed into the report, then reference them in your query. Lastly, in text boxes of the report designer (including table fields), there is a date format property, which determines the way that dates are actually displayed. ---
**Author:** ![enault](https://avatars.discourse-cdn.com/v4/letter/e/b5ac83/32.png) [@enault](https://forum.inductiveautomation.com/u/enault)\ **Post date:** [May 19, 2017, 3:59pm UTC](https://forum.inductiveautomation.com/t/report-help-basic-sql-query-sql-query/14314/3 "2017-05-19T15:59:09Z")
Well Thanks! Adding 2 parameter {FormatedStartDate} {FormatedEndDate} and using them in the query instead of {StartDate} {EndDate} fixed the date formating issue in the report. Im not sure i understand why this is working, I guess {StartDate} and {EndDate} being default parameter, can’t be formated the way i need them. ---
**Author:** ![adam.morales](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/adam.morales/32/3405_2.png) [@adam.morales](https://forum.inductiveautomation.com/u/adam.morales)\ **Post date:** [May 19, 2017, 6:31pm UTC](https://forum.inductiveautomation.com/t/report-help-basic-sql-query-sql-query/14314/4 "2017-05-19T18:31:53Z")
The StartDate and EndDate parameters point to Date objects. In most queries in Ignition, these get nicely coerced to syntactically correct strings, but in reporting they do not, so you must create the string representation yourself. ---
**Author:** ![jacque\_trahan](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jacque_trahan/32/58886_2.png) [@jacque\_trahan](https://forum.inductiveautomation.com/u/jacque_trahan)\ **Post date:** [February 3, 2024, 5:51pm UTC](https://forum.inductiveautomation.com/t/report-help-basic-sql-query-sql-query/14314/5 "2024-02-03T17:51:09Z")
I have this same problem. Is this a bug? Because if I hard code the output of those parameters into my nested query they work fine, If I make my SQL query have parameters from the named query they work fine. But if I use the report's parameters everything fails apart. the "jan" error comes up What I have tried: report prams set up: Start date(end is the same but uses now()): > dateFormat(dateArithmetic(now(), -30, 'days'), 'yyyy-MM-dd HH:mm:ss') > ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/8/782b4cd5ff204d4da66244555159b7ee3858f08e.png) Source on how i found to set this up: [Working with Dates](https://docs.inductiveautomation.com/display/DOC80/Basic+SQL+Query) I tried making them strings as well and still get a fail When that did not work I took off date format there and tried to format it in the nested SQL call all failed: toDate() dateFormat() Convert('date',) CAST({ChartStart} AS DATETIME) I tried to do > AND datColumn BETWEEN ? AND > ? > But since this is a nested Basic SQL query in the report the boxes do not show up for me to put the report parameters in. > For fun I tired this and failed: > AND datColumn BETWEEN {?} AND > {?} I have look at all the following documentation but can not find any other examples on why the SQL query does not except the reports date formatted parameters or how to deal with them: [Report Parameters](https://docs.inductiveautomation.com/display/DOC80/Report+Parameters) [Report prams video in the university](https://inductiveuniversity.com/videos/parameters/8.1) [Ignition User Manual 7.9 ... Report Data SQL Query](https://scada1.icontrolservices.com/main/system/help/SQL_Query.html) **Edit:** Instead of a Basic SQL queries used a SQL query. I was able to use the ? in the SQL call AND datColumn BETWEEN ? AND ? but I get this error now: > ERROR: class java.lang.String cannot be cast to class java.util.Date (java.lang.String and java.util.Date are in module java.base of loader 'bootstrap') I also tried to play around with the parameters making them strings, date, getting rid of the dateformt etc. On day 3 of trying to solve this with no luck. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [February 3, 2024, 6:57pm UTC](https://forum.inductiveautomation.com/t/report-help-basic-sql-query-sql-query/14314/6 "2024-02-03T18:57:45Z")
`dateFormat()` returns a string. Don't do that. You need to supply a date object for date parameters. Use `?` without curly braces to pass parameters in a report's normal SQL query. Basic queries need curly braces, but those convert to strings, which screws up dates. ---
**Author:** ![jacque\_trahan](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jacque_trahan/32/58886_2.png) [@jacque\_trahan](https://forum.inductiveautomation.com/u/jacque_trahan)\ **Post date:** [February 3, 2024, 8:21pm UTC](https://forum.inductiveautomation.com/t/report-help-basic-sql-query-sql-query/14314/7 "2024-02-03T20:21:05Z")
Final output. I tried this on day one before I posted an no clue why it is working now and did not work 3 days ago. ignition reset 2 hour timer was running so I know it was not that: Reprot pram set up: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/a/9af3c79c5ca52da58ec2e0477bcc588e59115749.png) query: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/5/8578d540c857a7ef260d25c193658a5d1a71e87f.png) **Edit:** I tired everything to recreate the issue I was having and can not seem to replicate it. So I am leaving my above post, there is a lot of good information there, but I just can not say what the root problem was for me. That said I am glad it is fixed now.