Defaulting dates in SQL questions

I’ve run into the situation a few times now where I have a question written in custom SQL (that’s too complex for the native query builder) that I need to allow the person accessing it to set a date, and if the date isn’t set then default to today.

I know that I could work around this by making the field required and not filling in a default, so that the person has to set something every time and I also know that I can set a default as an absolute date. But 95% of the time they’re accessing this specific question, it should be with today’s date plugged in.

Does anyone have any suggestions on how I can work out the SQL so that when the template doesn’t appear it fills now() or other methods on how to handle this?

1 Like

Here’s an idea. This example will only return one row, but you could extend to more pretty easily (like just treating this as a subquery and joining it to something else).

It uses the SQL parameters feature. The double brackets means the clause is optional, so if the user doesn’t input a specific date, the bracketed code won’t be evaluated. If it’s not evaluated, is defaults to today’s date by ordering by date.

select created_date from my_table [[where created_date = {{x}}]] ORDER BY created_date DESC LIMIT 1

Ah, interesting. Seems like we could enhance the default value selection by allowing relative dates instead of just specific ones. I’ve opened up a new issue to track this idea.

That’s a good suggestion! It makes my SQL more convoluted than I’d prefer, but at least until the enhancement of supporting today that @maz suggested on github happens, it would solve my problem in a reasonable way.