Hello community,
Is there a way to filter in a query on a text field with date time other then changing the datatype.e.g. a field lastUpdated with text datatype has a value 8/22/2021 2:55 PM GMT+02:00.If the filter is configured like:
filters: { a!queryFilter( field: "startDate", operator: "<", value: "9/22/2021 2:55 PM GMT+02:00"
it works fine if the month in the value is a one digit.it fails if the month in the value is two digits e.g.: "10/22/2021 2:55 PM GMT+02:00"
Is it possible to convert/alter the startDate value before applying the filter, or add a column to to the result with the date time converted to date time.
Kind Regards,
Erik
Discussion posts and replies are publicly visible
You would need to do that in DB. Appian can not modify the data in DB before querying it. Did you try to create a view which turns that string into a proper datetime?
thanks for your reply Stefan. I do not have rights to alter the DB or create a view. But I will suggest it to someone who can. I guess he will change the datatype anyway. I noticed that comparing date-time in text format works fine in an expression. But unfortunately not in the filter.
This does not work in a filter because Appian passes this to the DB for evaluation. And as the integration to DB is very generic, it fails.
Thanks for the explanation Stefan.