Report Help Basic SQL Query/SQL Query

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

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.

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.

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.