Hi I have a scenario wherein I am displaying data in a grid on SAIL f

@komalc Here goes the syntax which is one of the ways of handling it:

a!queryEntity(
entity: cons!MY_ENTITY,
query: a!query(
selection: ,
logicalExpression: a!queryLogicalExpression(
operator: "AND",
filters: {
a!queryFilter(
field: "",
operator: ">=",
value:
),
\ t a!queryFilter(
field: "",
operator: "<=",
value:
)
}
),
pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: - 1
)
)
)


Example:
a!queryEntity(
entity: cons!MY_ENTITY,
query: a!query(
selection: null,
logicalExpression: a!queryLogicalExpression(
operator: "AND",
filters: {
a!queryFilter(
field: "created",
operator: ">=",
value: datetime(2014,09,05)
),
\ t a!queryFilter(
field: "created",
operator: "<=",
value: datetime(2014,09,06)
)
}
),
pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: - 1
)
)
)

Also Appian has specified how to deal with date field at https://forum.appian.com/suite/help/7.9/Query_Recipes.html#Querying_on_Multiple_Conditions. So accordingly you may also develop a way to handle date time and time stamps.

Still if you are struck, please add some more info to your post such as the type of column in your cdt, sail component (a!dateField or a!dateTimeField) used to capture the filter values and let's see if any other practitioners could come up with better responses.

Hi Komalc, please try to see if we can try to use the below condition in the filter
a!queryFilter(field:"<FieldName>", operator:"BETWEEN", value:{ri!fromDate, ri!toDate})

As per my knowledge, if both of the filters are mandatory BETWEEN can be used, else >,>=,<,<= can be used.

Thank you sikhivahans narasimhaadityac for replying!!

sikhivahans I tried using datetime function but if user selects date 12/30/2015 i.e. 30th December 2015, the datetime function converts it into 12/29/2015 7:00 PM EST.
Below is the code which will explain my scenario better (1st is the user filter and then I am just displaying the value in text field to view what is actually been returned by datetime function):

a!dateField(
                    label:"Start Date",
                    value:local!date,
                    saveInto:local!date
                    )
                    
          a!textField(
                    value:if(isnull(local!date),{},datetime(year(local!date), month(local!date),day(local!date),hour(local!date),minute(local!date),second(local!date)))
          )
          

Irrespective I use or don't use hour(),minute(),second(); I am getting the same result.

Can you please guide me.

Thank you in advance!!

@komalc Could you please try fn!userdatetime() instead of fn!datetime() and do let me know what has happened?

I assume that the results you're getting are because Appian's global time is configured to be GMT-5. Is that correct?

@komalc "Irrespective I use or don't use hour(),minute(),second(); I am getting the same result." - To the best of my knowledge, this is an expected behavior. The documentation at https://forum.appian.com/suite/help/7.11/Date_and_Time_Functions.html#datetime.28.29 under 'Notes' and 'Examples' conveys the same and at the same time, an alternative function namely 'userdatetime()' is also specified. Hope this would be of some help to you.

Thank you sikhivahans, Nick for you replies!!

Nick, the Primary Timezone in my settings is: UTC-5:00


sikhivahans, I tried using userdatetime(), the date now I got as a result is the same as that I selected from the calendar but irrespective I use hour(),minute(),second(); I get the time as 12:00 AM EST i.e. if I selected date 12/23/2015 from the calendar, I get the result with userdatetime() as: 12/23/2015 12:00 AM EST.

Also if the date stored in DB is:
2015-08-20 00:00:00
and I simply display this column in my a!textfield() I get the result
8/19/2015 8:00 PM EDT.

Can you please guide me.

Thank you in advance!!

The reason why userdatetime returns midnight on that day is because if you do not pass an hour, minute, and second, it will default to 0 for those 3, and the zero hour, zero minute, and zero second evaluates to midnight.

Can you please attach the SAIL where you're seeing the 8:00 PM EDT behavior? What timezone is your database configured at? If it is configured at UTC-1 then it would explain why we're seeing the value returned 4 hours in the past.

Hi Nick
Thank you for the explanation of userdatetime().
I have used userdatetime() as the data being displayed on my SAIL is on the basis of this only.