Using a Date in a Aggregate filter

I am trying to create a filter in a aggregate. I got a field (date_of_service). I want the filter to only return rows where the date_of_service is 7 days or more less than today.

I have tried using SYSDATETIME, but the interface tells me "Unknown object SYSDATETIME in expression". I am sure there is some simple way to do this, but so far I can't find a answer.

Solution

in the filter of your aggregate you have to write something like this:

table.date_of_service <= AddDays(CurrDate(),-7)

I hope it will helps you!


Thanks and Best Regards,

Nuno Pereira

Solution

I found something that works. Just using this:

date_of_service <= CurrDate() - 7

did you test it? 

Yes it worked.

Nice! maybe outsystems fixed it in 11 version, I think that what you're doing doesn't work in some other versions.

Best Regards,

Nuno Pereira

Yes, it doesn't work in 11 version:


you have to use this:


Thanks and Best Regards,

Nuno Pereira


Sorry didn't have my system in front of me. This is what I found worked:

washjob.date_of_last_job < NewDate(2019,3,10)

Okay, but in your case it will work just for today.

It does not the best way to do this, because your "NewDate(2019,3,10)
" is hardcoded, it won't work tomorrow, just if you are incrementing your date all days in your code.

If you want to compare the current date less 7 days, i advise you to use "AddDays(CurrDate(),-7)". You won't need to change your code, it will work, today, tomorrow and ever.

Thanks and Best Regards,

Nuno Pereira


This doesn't generate a error: 

washjob.date_of_last_job <= NewDate(2019,3,10)


But when I do this: 

washjob.date_of_last_job <= AddDays(CurrDate(),-7)

I get the error "Operator '<=' cannot be applied to operands of type Date and DateTime. The problem is my field is of type date and AddDays returns a DateTime. Maybe there is a way to convert DateTime to Date?



Thomas Giglia wrote:

This doesn't generate a error: 

washjob.date_of_last_job <= NewDate(2019,3,10)


But when I do this: 

washjob.date_of_last_job <= AddDays(CurrDate(),-7)

I get the error "Operator '<=' cannot be applied to operands of type Date and DateTime. The problem is my field is of type date and AddDays returns a DateTime. Maybe there is a way to convert DateTime to Date?




DatetoDateTime(washjob.date_of_last_job) <= AddDays(CurrDate(),-7)

use this.

thanks and best regards,

Nuno Pereira

Here is what I had to do to get they data types to match up

washjob.date_of_last_job <= NewDate(Year(AddDays(CurrDate(),-7)), Month(AddDays(CurrDate(),-7)), Day(AddDays(CurrDate(),-7)))


Thomas Giglia wrote:

Here is what I had to do to get they data types to match up

washjob.date_of_last_job <= NewDate(Year(AddDays(CurrDate(),-7)), Month(AddDays(CurrDate(),-7)), Day(AddDays(CurrDate(),-7)))


no, don't make it. it's very inefficient too. Please see on my last comment: 

DatetoDateTime(washjob.date_of_last_job) <= AddDays(CurrDate(),-7)