How to use a date range parameter in custom SQL

Let’s say you’d like to have a custom SQL table in your Workbook filter out dates in a DATE column according to any of the many possible selections in the date range parameter (pictured below):

This can be accomplished using some iteration of the following CASE/WHEN statement in custom SQL:

CASE WHEN date({{Date-Parameter}}:start) IS NULL
THEN DATE <= date({{Date-Parameter}}:end)
WHEN date({{Date-Parameter}}:end) IS NULL
THEN DATE >= date({{Date-Parameter}}:start)
ELSE DATE BETWEEN date({{Date-Parameter}}:start) AND date({{Date-Parameter}}:end)

1 Like

:exploding_head: This is awesome! Could you use the same start/end references for embedding?

I haven’t ever done that before, but it would be nice if it’s possible. I’ll give it a go and get back to you!

I’m hoping I understood what you meant by “the same start/end references”… but anyway, I made a user-backed embed with a date range parameter control and a custom SQL table (as described in this post)… and it seemed to work right out of the box with the published control selection. Please let me know if this answers your question.

These notes should be in our documentation soon but in the meantime:
Custom SQL quirk based on data warehouse dialect (given we have a control with id “Date-Range-Control”)

i. Snowflake: the parameter is a VARIANT
Ex: to_timestamp({{Date-Range-Control}}[‘start’]) or to_timestamp({{Date-Range-Control}}:start)

ii. BigQuery, Trino, Databricks: the parameter is a STRUCT
Ex: {{Date-Range-Control}}.start

iii. Redshift: the parameter is a SUPER
Ex: select date_range.start from (select {{Date-Range-Control}} date_range)::timestamp

iv. Postgres: the parameter is a JSONB
Ex: ({{Date-Range-Control}}->>‘start’)::timestamptz


If you want to manually extract the start and end values of your date range parameter, you can convert the parameter to JSON and do an extract. Below example uses Snowflake functions:


1 Like

Added Custom-SQL, Parameters

Thanks Brett,

I spent more than 3 hours struggling to find an answer of how to treat date range parameters in Redshift. It is a pity that Sigma Computing is so limited

1 Like