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.
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.
Nuno Reis wrote:
Thank you Nuno.
It is working foe me.