SQL Query with Date in where clause from global variable

Noah
Noah Registered Posts: 43 ✭✭✭✭

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?

Answers

  • Zach
    Zach Dataiker, Dataiku DSS Core Designer, Dataiku DSS Adv Designer, Registered Posts: 153 Dataiker
    edited July 17

    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)

Setup Info
    Tags
      Help me…