SQL Query in Metabase - show today data or date select data

Question

I have some issue with my query when I run it in Metabase. I want to show list data baseon datetime. The result I want is as default the list will show today data, but when I select another date from date filter it will show the data list from selected date.

the data work well with default today data (ex : oct 16, 2020), but when i select another date (ex : oct 15, 2020) the data show oct 15, 2020 data and oct 16, 2020 data.

here is my query :

select display_name as Name, time_in as Clocking_Time, 
from Clocking_tbl
WHERE time_in = cast(getdate() as Date) [[or time_in = cast({{ValidDate}} as date)]]
order by time_in asc

Please advice how to fix it.

thank you

Answer 1

Do you want the default to be today's date when the input value is NULL?

where time_in = coalesce(cast({{ValidDate}} as date), cast(getdate() as Date) )

Answer 2

select display_name as Name, time_in as Clocking_Time, 
from Clocking_tbl
WHERE {{date}}
order by time_in asc

You can use variable type "fieldfilter",field to map to "your table (Clocking_tbl) and date field (time_in)", filter widget type "Date Filter", click button required then default filter widget value choose "current day"

Let me know if you have any other questions :)