SQL Query with Date in where clause from global variable

Noah · April 5

I am have a SQL query in dataiku set up like so:

SELECT *
FROM "dbo"."Table"
where "Date" BETWEEN CAST(${prior_val_date} as date) AND CAST(${val_date} as date)

I am getting this error message:

Validation failed: Query failed: Explicit conversion from data type int to date is not allowed.

my variables are defined like so:
{
"prior_val_date": "9/30/2023",
"val_date": "12/31/2023",
}

what do I need to get this to work?

Zach · April 7

The variables are being parsed as numbers because you haven't enclosed them in 'single quotes'

SELECT *
FROM "dbo"."Table"
where "Date" BETWEEN CAST('${prior_val_date}' as date) AND CAST('${val_date}' as date)

Answers