Date not working in advanced search

[lara]

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:

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:


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?

[rpetribu]

Hi @lara

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!

[lara]

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:

[lara]

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

Thank you so much for your time!

[lara]

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

[rpetribu]

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

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

And lower than the first hour of the next day.

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

Make sense?

[lara]

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