Redash Illegal Type of Parameter

[msadiq]

Hi,

I have redash 8.0.0+b32245 (a16f551e) connected to Clickhouse 22.9.3.18.
I am trying to run the query below with a date parameter.

SELECT C.REVENUE_DATE, C.BUSINESS_DATE, CUR_ROOMS/257 AS CUR_OCC, sum(NO_DEFINITE_ROOMS) AS CUR_ROOMS, sum(REVENUE) AS CUR_REVENUE, CUR_REVENUE/CUR_ROOMS AS CUR_ARR, CUR_OCC * CUR_ARR AS CUR_REV_PAR
FROM rktaboxx_db.market_statistics_daily C
WHERE C.REVENUE_DATE = {{ rpt_revenue_date }}
GROUP BY REVENUE_DATE, BUSINESS_DATE
ORDER BY C.BUSINESS_DATE

Unfortunately I get the error below.

When I remove the parameter and use a literal date like ‘2022-11-03’ the query is successful.

Please advise how to get this to work correctly.

Best regards,
MS

[xavier-d]

I think SQL is trying to perform a subtraction
look at the error message: ((2022 - 11) -3)
Try to add quotes to your variable
WHERE C.REVENUE_DATE = '{{ rpt_revenue_date }}’

or use the convert function

WHERE C.REVENUE_DATE = CONVERT(DATETIME,'{{ rpt_revenue_date }}')

[msadiq]

Hi @xavier-d

Thank you so much. It was that easy!