I have a CDT with field dateTime of type date and time. In the database the corresponding column has type timestamp and defaults to '0000-00-00 00:00:00', and currently this is the only value in the column, even though it occurs multiple times. When I try to use query editor to query the dateTime column, I get an error saying "An error occurred while retrieving the data." What is the cause of this error and how might I avoid it?
Discussion posts and replies are publicly visible
Appian's query entity function does not work well with dates like 0000-00-00 or timestamps in the format that you have noted. To deal with the error immediately, null those columns out and make sure any defaults are either null or a valid timestamp
Thank you for the response. If I were to fill the column with nulls, surely this would cause an issue with aggregating the results, say if I wanted to get the minimum of the dates? Is there anyway I can resolve the main issue without losing this capability?
Would filtering out any null dates in a queryFilter, and then aggregating, work for you? Or would you actually want a null/0000-00-00 date to be considered a minimum value?
I need to see null/0000-00-00 as the minimum value, yes
instead of giving 0000-00-00 try giving some old date which you know it wont be used in the application and aggregate with that.