Unable to filter table by date if datetime has anything othat than "T00:00:00.000Z"

App Building
,
1

I had this issue earlier at work so I've quickly made a test on the Retool cloud for this post. I'm unable to filter my table by date if a datetime column has anything other time written to it other than "T00:00:00.000Z".

I might be writing it incorrectly but I've tried grabbing the date value from the date component and throwing a wildcard on the end to grab any time but it doesn't display any data.

select * from products
where products.created_at = {{date1.value + '%'}}

In this example, the 30th shows data but the 29th returns blank.

capA
capA1431×448 25 KB

capB
capB1450×480 34.6 KB

capC
capC980×80 10.3 KB

2

Hi @rcanpolat!

I think when the date from the calendar is converted for the comparison, it gets the midnight time you referenced. To pull all products created during that day, you could do something like the below. It just might change a little depending on your SQL database, but this works for me in Postgres.

WHERE created_at BETWEEN '{{date1.value}}'
  AND DATE('{{date1.value }}') + INTERVAL '1 day';