Error in Dataset with datetime parameter

Question

Hi,

I am trying to add dataset with parameters and column is datetime field. So, I am getting this error while saving that dataset.

"Conversion failed when converting date and/or time from character string."

Query for this dataset is this:

select * from xyz where  xyz.ReceiveDate>=$P{par_Recievedate1}  and xyz.ReceiveDate<=$P{par_Recievedate2} 

Answer 1

Hi,

    the error refers to wrong syntax in query, please check your database SQL dialect documentation.

I suggest to define a string parameter and use it in query as it's substituted by an SQL string within starting and ending apostrophes.

E.g. for MySQL, given query

select * from salary where pay_date >= $P{par_pay_date}

 and string parameter

par_pay_date=1998-01-01 00:00:00

as default value, the final query executed  by Knowage on database is

select * from salary where pay_date >= '1998-01-01 00:00:00'

As I said, please check your database SQL dialect documentation in order to use the proper query syntax.

Best regards,

Francesco