I am trying to run a basic SQl query in the reporting module but the data type Date for the start and stop times is producing a string for the date. Is there a way to get a DateTime date type on these parameters?
Maybe show us how you are assigning values to your parameters and the query?
If you are using something like the date time input then the value property should be a datetime, but if you are using the formatted value property that will be interpreted as a string.
You also have options in the query to convert to a date time, something like this as an example:
CONVERT(DATETIME, ?, 120)
This is my query.
SELECT BPR_B40DieNum,
BPR_B40BilletTotal,
avg(BPR_B40ExtrudeTime) AS âAverage_ExtTimeâ,
avg(BPR_R2R_Active) AS âAverage_R2Râ,
avg(BPR_R2R_Active) / avg(BPR_B40ExtrudeTime) AS âR2R_Percentâ
FROM Epics.foy.SCM_DW_Boss_Billet2
WHERE EventTime >= convert(DateTime,{[default]Reports/Reports_12HrStartTime.value},120) AND EventTime <= convert(DateTime,{[default]Reports/ReportsStopTime.value},120) AND BPR_B40BilletCount > 3 AND
BPR_B40ExtrudeTime <= 299 AND Bpr_B40BilletCount < Bpr_B40BilletTotal
GROUP BY BPR_B40DieNum,
Bpr_B40BilletTotal
this has the convert option that you suggested in it as well. The tag values are from two expression tags with data type DateTime, StopTime expression of now() and the start date uses a dateArithmetic of -24 âhrâ from the stop time.
Next time use the preformatted text button </> for better readability. Ok, so you arenât using parameters at all (those are denoted by â?â in your query). I donât see a reason that wouldnât work, though you donât need two tags. You can just use the stop tag and do date arithmetic in the query.
Something like this:
DATEADD(hour, -24, StopTime)
You should probably parameterize theses. An example Note the question marks in the query, with the parateres at the bottom of the :
That did it!! Thank you very much Jordan.
1 Like