7.8.2 Report Module Datetime Issue for MS SQL

Ignition
1

I’m trying to do a simple query based on time, but I am having no luck.

I’m using the defaults that are put in automatically for StartDate and EndDate.

In the Preview Window, I get the following format for StartDate:

2016-04-04 08:27:15

But, when I run the following query:

SELECT * FROM tbl_turbineData WHERE t_stamp > '{StartDate}'

I get this date format, which MS SQL doesn’t like…

Mon Apr 04 08:29:22 EDT 2016

I usually get the following warning:

WARN: Simple Query could not be completedConversion failed when converting date and/or time from character string

Is there something that I’m missing?

2

I could be way off on this one, but here goes.

Firstly, if you want to use a parameter, in this case StartDate, try changing your SQL query to:

SELECT * FROM tbl_turbineData WHERE t_stamp > ?

The ‘?’ will unlock the Parameter 1 field below the query where you can now select {StartDate} from the defined Parameters (3rd icon down)

If you are still having problems with the SQL formatting of the date and depending on your t_stamp field and SQL version, you may need to use the CAST (expression AS data_type) Function.

I do and use the following to get all fields where the day was yesterday.

SELECT * FROM sometable WHERE CAST(date AS DATE) = CAST(GETDATE() - 1 AS DATE)
3

First solution helped. I had been trying this before, but I was under the Basic SQL Query, which doesn’t support that.

Thanks