Date not working in advanced search

I am creating an advanced search filter in my application. And the problem I’m facing is searching through dates. The logic I did was to create 2 custom states in list format from my database. A general list that brings all the data and another list that brings the advanced search data. In the advanced search, I associate the fields that are available for search, such as the creation date field. When I associate the Creation Date field with the Date picker, it does not bring me the results.

Here are the screenshots of how I do this search in the database:
image


But when I do the search, it doesn’t bring up the results. I noticed in the debug that even though I put the dd/mm/yyy format in the date time picker, it is bringing a time in the filter. This way:
image
image
I don’t know if the fact that I’m showing this time in the filtered could be causing the information not to be found. Is there a way to remove this time, besides formatting the date time picker? Or could I be doing something wrong?

Hi @lara :wave:

The DATE is always a combination of DATE and TIME.

So if you want to filter the results based on the day you are selecting you will need to do the following constraint:

  • Creation Date >= DatePicker’s Value :Rounded down to Date
  • Creation Date < DatePicker’s Value + 1 Day :Rounded down to Date

Hope it helps!

1 Like

hey @rpetribu thank you for your reply!

Maybe I didn’t quite understand what you meant… because I tried to do as you suggested and it’s giving me this error:


:frowning:

@rpetribu sorry… It was my mistake! I managed to solve it. I did it this way as you suggested:
image

Thank you so much for your time!

1 Like

One question… why doesn’t this work with = ? and only with greater equals or less equals? because I would like to search the exact date

You can.

But as the DATE type is always a combination of Date + Time, when you constraint your search only with “=” operator, you are searching only for the records created in that exactly time (12:00) of that exactly date (12/02/2024). What I assume that is not what you want…

When you use >= and < operators you can contraint the entire day, regardless the time:

Creation Date >= DatePicker’s Value :Rounded down to Date

:point_up: Have to be greater than the first hour of the selected day

:point_down: And lower than the first hour of the next day.

  • Creation Date < DatePicker’s Value + 1 Day :Rounded down to Date

Make sense?

Yes! Now I can understand the logic better! Thank you very much for your attention and excellent explanation!