Issue in filtering start and end date
Question
Application Type
Reactive

I have used the following condition in a aggregate 

(User.Creation_Date >= Start or DateTimeToDate(Start) = NullDate()) and (User.Creation_Date <= End or  DateTimeToDate(End) = NullDate())

Ex: 2021-03-02 & 2021-03-10 Iam not able to get the filtered data for enddate  but I have mentioned "<="

 

Solution

AddSeconds(AddDays(End, 1) , -1) -> This will get you your End Date (2021-03-10 23:59:59)

It adds a day and then subtracts a second :)

AddDays(End, 1) Will give you 2021-03-11 00:00:00 -> Should work as well


Let us know if this works!


Cheers,

Luis Matos

mvp_badge
MVP

Hi anugna,


When you are testing with a date without time, the value assumed will be 00:00:00 of that day.

So with your example, you're filtering by start date 2021-03-02 00:00:00 and 2021-03-10 00:00:00. So if your user was created at 2021-03-10 00:00:01 it won't be fetched.

Try with 2021-03-10 23:59:59 and you will get users created on 2021-03-10.


Kind Regards,
João

Hi @João Marques I have tried to put time in filter now iam able to get the data , but how can i add that to the filter condition in aggregate ,because I only select the date in the datepicker input widget in front end.

mvp_badge
MVP

You could do a transformation when selecting the date but to make it simple for your scenario you can compare the end date without time, like this:


(User.Creation_Date >= Start or DateTimeToDate(Start) = NullDate()) and (DateTimeToDate(User.Creation_Date) <= End or  DateTimeToDate(End) = NullDate())


Kind Regards,
João

Solution

AddSeconds(AddDays(End, 1) , -1) -> This will get you your End Date (2021-03-10 23:59:59)

It adds a day and then subtracts a second :)

AddDays(End, 1) Will give you 2021-03-11 00:00:00 -> Should work as well


Let us know if this works!


Cheers,

Luis Matos

Thanks Joao, I will try this method tooo 

Thanks Luis for the solution. It is working now

Hi,

This should be working for you if you allow selecting only date not with time:

User.Creation_Date < AddDays(End,1)

Regards

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.