Hi,
I have a field in my DB table which has DateTime value init. And I am trying to filter records using date only, of course its not working as expected, but when I do a search using same data (date only) by changing time value to "00:00:00" I am able to search. Can you help me with it?
SAIL
Is a Date Picker
Discussion posts and replies are publicly visible
shamima0001, you will can cast your DB field from DateTime to just Date. Once you do that, you'll be comparing Dates with Dates. Check the todate() function for that
docs.appian.com/.../fnc_conversion_todate.html
Can you explain a bit more, I did not understand what you are trying to explain.
SAIL:
a!queryEntity( entity: cons!SUMMARY, query: a!query( logicalexpression: a!queryLogicalExpression( operator: "AND", filters: { a!queryFilter( field: "startdate", operator: "=", value:ri!startDate ), a!queryFilter( field: "enddate", operator: "=", value:ri!endDate ) }, ignoreFiltersWithEmptyValues: true ), selection: a!querySelection( columns: { a!forEach( { "startdate", "enddate" }, a!queryColumn(field: fv!item) ) } ), pagingInfo: a!pagingInfo( startIndex: 1, batchSize: -1 ) ), fetchTotalCount: false ).data
Hi there. Sorry for that, I was picturing the opposite scenario. Feel free to take a look at the below post in Community.
community.appian.com/.../how-to-filter-date-using-query-entity
Although, it does filter data based on date provided. However, there is a problem with this approach. Where we dont provide any value in date picker it give me an error (below).
A null parameter has been passed as parameter 1.
a!queryEntity( entity: cons!SUMMARY, query: a!query( logicalexpression: a!queryLogicalExpression( operator: "AND", filters: { a!queryFilter( field: "startdate", operator: "=", value:{gmt(todate(ri!startDate)),gmt(todate(ri!startDate))+day()} ), a!queryFilter( field: "enddate", operator: "=", value:ri!endDate ) }, ignoreFiltersWithEmptyValues: true ), selection: a!querySelection( columns: { a!forEach( { "startdate", "enddate" }, a!queryColumn(field: fv!item) ) } ), pagingInfo: a!pagingInfo( startIndex: 1, batchSize: -1 ) ), fetchTotalCount: false ).data
Two things I noticed:
a!queryFilter( field: "startdate", operator: "between", value:{gmt(todate(ri!startDate)),gmt(todate(ri!startDate))+day()}, applyWhen: not(isnull(ri!startDate)) )