Filter datetime not working properly

Queries and Resources
, ,
1

I am trying to filter my google sheets data in a query and I am really banging my head here, as I can't find a way to properly filter my datetime fields.
I have a regular date filter that works perfectly so it's really about datetime.

In my case I want to retrieve entries between 2 time slots. After noticing I was getting wrong results, I stripped the formula down to 1 criteria and the issue shows already.
I have a datetime field in a form, that writes the data in my Google sheet. The data is written, then displayed completely correctly. However, I am not able to filter the table with this formula:

select * from {{table1.data}} where Startdatum >= {{ moment(Enddatum_input.value).format('YYYY-MM-DD h:mm a') }}

image
image1320×868 83.9 KB

The formula will retrieve items that are before my input field date. I tried so many things and searched through the forum, but I am now stuck.

I really like Retool a lot so far. This is my first use case and if this goes well, I will consider working a lot more with Retool in the future :slight_smile:

2

Hey @Oggy!

Would you mind sharing a screenshot of {{ table1.data }} or posting a copy of part of it here? It might help to see exactly what the data in your Startdatum column looks like when it comes back from sheets.

3

Hi Kabirdas, thanks for your reply!
Here is what the date looks like. I tried with two different format:

  • Enddatum: "4/21/2023 0:16:55" (raw from the sheet query)
  • Enddatum2: "2023-04-21 12:16 am" (transformed via moment)
4

:thinking: can you try using one of the following two snippets?

DATE(Startdatum) >= {{ moment(Enddatum_input.value) }}

DATE(Startdatum) >= {{ new Date(Enddatum_input.value) }}
5

Hi @Kabirdas,
The first snippet did it for me, now it works perfectly!

Thanks a lot!