Date filter based on the custom query

Hey, I'm trying to apply in practice Date variable filter in my custom query based on what I found in the official Metabase documentation:

SELECT *
FROM orders
[[WHERE created_at BETWEEN {{start_date}} AND {{end_date}}]]

However when I run:
SELECT
month_dt
, COUNT(CASE WHEN sales_role ='AS' THEN demo_scheduled ELSE NULL END) demo_scheduled_AS
FROM user_history AS u
LEFT JOIN opportunity AS o ON o.demo_creator_id = u.user_id AND date(o.month_dt) = date(u.month_dt)
WHERE 1=1
[[AND u.month_dt BETWEEN {{start_date}} AND {{end_date}}]])

I get the following error msg: '"No matching signature for operator BETWEEN for argument types: DATE, TIMESTAMP, TIMESTAMP. Supported signature: (ANY) BETWEEN (ANY) AND (ANY) at [127:16]", "reason" : "invalidQuery" } ], "message" : "No matching signature for operator BETWEEN for argument types: DATE, TIMESTAMP, TIMESTAMP. Supported signature: (ANY) BETWEEN (ANY) AND (ANY) at [127:16]", "status" : "INVALID_ARGUMENT" }'

It seems like my 'u.month_dt' is treated like a timestamp but this is in fact a date field. Any ideas on what could go wrong?

Thanks!

Which type of filter are you using? I suggest you use a field filter for this
SELECT *
FROM orders
[[WHERE {{created_at}}]

The field ' u.month_dt' comes from a custom query (does not belong to DWH table) therefore as far as I know I cannot use it as a field filter?

BTW I'm using a 'date' filter :slight_smile:

there might be some casting issue there, try doing

SELECT
month_dt
, COUNT(CASE WHEN sales_role ='AS' THEN demo_scheduled ELSE NULL END) demo_scheduled_AS
FROM user_history AS u
LEFT JOIN opportunity AS o ON o.demo_creator_id = u.user_id AND date(o.month_dt) = date(u.month_dt)
WHERE 1=1
[[AND u.month_dt BETWEEN {{start_date}}::timestamp AND {{end_date}}::timestamp]])

you might want to go to the DB and see the query that metabase is firing to get the full picture of this