Unable to retrieve data from queryEntity for a date range

Question

Unable to retrieve data from queryEntity for a date range. The below code is not working and instead throwing an error that "between" operator cannot be used. Please advise how to fix it. 
 local!chartDatasubset: a!queryEntity( entity: cons!NSARequest_Entity_Cons, query: a!query( aggregation: a!queryAggregation( aggregationColumns: { a!queryAggregationColumn(field: "Status", alias: "Status", isGrouping: true), a!queryAggregationColumn(field: "SubmittedDate", alias: "SubmittedDate", isGrouping: true), a!queryAggregationColumn(field: "Id", aggregationFunction: "COUNT") } ), filter: a!queryFilter( field: "SubmittedDate", operator: "between", value: { if(isnull(ri!StartDate), today(), ri!StartDate), if(isnull(ri!EndDate), today(), ri!EndDate) } ), pagingInfo: local!chartPagingInfo ) ),

Initially had an expression rule below which used to be working in 18.3 version. Not sure if in new 18.4 version it isn't. Please advise. 
 
 with( local!chartPagingInfo: a!pagingInfo( startIndex: 1, batchSize: -1, sort: a!sortInfo( field: "SubmittedDate", ascending: true ) ), a!queryEntity_18r3( entity: cons!NSARequest_Entity_Cons, query: a!query( filter: { a!queryFilter( selection: "", field: "SubmittedDate", operator: "between", value: { ri!StartDate, ri!EndDate } ) }, pagingInfo: local!chartPagingInfo ) ) )

Mike Schmitt · Answer

I believe the if() statements you're using in the filter are causing the issue. The type (107) that it's complaining about is "list of date", so i suspect your from: and to: dates are resolving as an array of 1 date each (as opposed to each one being a single element like it's expecting). I can't verify this, but I suspect your if() statements might be returning an array/list and this might be causing the issue. 
 
To quickly check this, please replace that line of code with this and try again: 
value: { if(isnull(ri!StartDate), today(), ri!StartDate)[1], if(isnull(ri!EndDate), today(), ri!EndDate)[1] }

John M · Answer

What is the type of SubmittedDate? Have you tried changing today() to now()? It seems like your SubmittedDate when comparing to today() is invalid because they are of differing types. However, if you change today() to now() (probably would want to truncate the now() timestamp to midnight of the current date) it appears to work as expected from my test scenarios.

sindhug · Answer

Hi Anusha, 
 
Please check data type of the SubmittedDate, if it is Date type , replace value with below code. Then it should work as expected. 
 
value:{ if(isnull(ri!StartDate), today(), ri!StartDate), if(isnull(ri!EndDate), today(), ri!EndDate) } 
 
Thanks, 
Sindhu

aloks0189 · Answer

Hi anushas0002 your rule input i.e. ri!startDate and ri!endDate should match with the type of your DB column data type i.e. submittedDate (Date and Time). 
 
Hence to resolve this you have following ways: 
 
1. [Recommended] change your input type to Date and Time in Appian and pass the respective value to it and also make sure that you are handling the null values properly. 
 
2. [Not Recommended] If you don't want to change your input type in such case, you need to enclose ri! startDate and ri!endDate with todatetime() function as shown below: 
 value: {todatetime(ri!startDate), todatetime(ri!endDate)} 
 
-- This will convert your Date input value to Date and Time. But i would not recommend to go for this approach, as the Approach 1 stands better here. 
 
3. [Not Recommended] If you don't want to change your input type in such case an another approach could be: Alter the Database column type to Date and re-map your CDT and verify your data store (to avoid any mapping issues). But i would not recommend to go for this approach, as the Approach 1 stands better here. 
 
Hope this will help you.