Get data between two dates in aggregate

I have a column of datetime and i need the data from the aggregate by passing from date and to date.

I am giving the filter : 


PatientVisitCounts.VisitedOn >= DateToDateTime(FromDate) and
PatientVisitCounts.VisitedOn <= DateToDateTime(ToDate)

Suppose from date is 17-01-02019 and to date is 23-01-2019 and is giving me the result till 22. Not till 23.

Can anyone please help.

Regards,

Hi Mohammad,

As you are using only the DATE part, but internally everything is DATE AND TIME, you are saying that you want everything UP TO 23-01-2019 00:00:00.

To include any date that is <= 23-01-2019 23:59:59, use the AddDays function and change the <= to <

PatientVisitCounts.VisitedOn >= DateToDateTime(FromDate) and
PatientVisitCounts.VisitedOn < AddDays(ToDate, 1)

By the way, I would do the conversions outside the aggregate.

Cheers.

Solution

Hi Mohammad.

Check the description of DateToDateTime function. It assumes time as 0:00.

Unless the visit was exactly at 0:00:00, 23-01-2019 hh:mm:ss is greater than 23-01-2019.

You must use BuildDateTime and pass it 23:59:59 as time.

Solution

Nuno Reis wrote:

Hi Mohammad.

Check the description of DateToDateTime function. It assumes time as 0:00.

Unless the visit was exactly at 0:00:00, 23-01-2019 hh:mm:ss is greater than 23-01-2019.

You must use BuildDateTime and pass it 23:59:59 as time.

Thank you Nuno.

It is working foe me.

Cheers.