Survey banner
The Dataiku Community is moving to a new home! Some short term disruption starting next week: LEARN MORE

SQL Query with Date in where clause from global variable

nshapir2
Level 2
SQL Query with Date in where clause from global variable

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?

0 Kudos
1 Reply
ZachM
Dataiker

Hi @nshapir2,

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)

 

0 Kudos