Correct way to use date filters in SQL custom query

I'm trying to make custom SQL using date filter.
But the values I get from the result are wrong.
I need to make comparisons involving the year and/or month.

I already tried with the EXTRACT(YEAR FROM {{date_filter}}),
and
select count(*) from matricula WHERE (matricula.data_contrato >= date_trunc('year', CAST({{data}} AS date)) AND matricula.data_contrato < date_trunc('year', CAST((CAST({{data}} AS date) + (INTERVAL '1 year')) AS date)))
and some others stuff.
The results for any year in this case, are all the same.

I'm using postgresql and metabase v0.39.1

Hi @notBatman
I am Batman!
I'm not sure what it's not working - it should work. Which version of Postgres and what is the column type of matricula.data_contrato
But since you're basically just returning 1 year, then you could do something like this instead:

select count(*) from matricula
where date_trunc('year', data_contrato) = date_trunc('year', {{data}}::date)

@flamber I realy dont know whats going on (╯°□°)╯︵ ┻━┻.
Now it works (the column type is a date).
Is there any limitation when selecting the query and executing it, or only executing it without selecting anything?
Apparently this is the problem.

image
image

@notBatman You have a default value for the filter - that took me a little while to guess.
Batman has created an issue:
https://github.com/metabase/metabase/issues/16584 - upvote by clicking :+1: on the first post

1 Like

@flamber
Yes, I have a default value. Sorry I didn't report this earlier.
Thank you very much for the support.