SQL Statement within Attribute Rule Calculation Error

[OliverSandoval_p]

Hello Everyone, 

I'm having an issue with an sql statement within a calculation attribute rule. So basically I want to filter related data based of guid and a 'last clean' date field that's not null, AND is also within this year. The first two statements are fine on thier own, but the calculation fails when I query for year. 

Here is a part of my code for context

var CB_gid = $feature.GlobalID;
var this_year= Year(now());
var filter_statement = 'guid = @CB_gid AND last_cleaned IS NOT Null AND YEAR(last_cleaned) = @this_year)'

//an example of this which works fine in pro can be seen below
guid = '{F5C7CC47-6B71-439A-A144-834C9696C262}' AND last_cleaned IS NOT Null AND YEAR(last_cleaned) = '2022'

This a part of the error that occurs when trying to run the calculation.

Just for some more context, this filter is just one of possible three filters that occurs based off three if statements. The other two work fine, just not this one. And there is also a workaround I can do with more arcade variables for beginning of the year date and end of year date, but id rather have it in sql statement.

Any help would be greatly appreciated!

Thank you!

[JohannesLindner · Accepted solution]

According to your working query, 2022 should be provided as string, but it's an integer.

Try these approaches:

// convert this_year to string
var CB_gid = $feature.GlobalID;
var this_year= Text(Year(Now()));
var filter_statement = 'guid = @CB_gid AND last_cleaned IS NOT Null AND YEAR(last_cleaned) = @this_year)'

// take care of the types yourself
var filter_statement = `guid = '${$feature.GlobalID}' AND last_cleaned IS NOT Null AND YEAR(last_cleaned) = '${Year(Now())}')`