Known Issue: DateTime values in Live Search or Filtering not returning expected results
KB003665
PRODUCTIssue
When filtering data on DateTime values using the search popup or the filter properties in the Picker control, or when enabling the Live Search property of the Auto-Complete control and searching by date, control values are not returned as expected. This applies to SQL-based SmartObjects, for example SmartBox SmartObjects.
Cause
The SmartForm controls build a contains text filter which gets applied to a DateTime property type. When applying a text-based filter on server side in SQL, the text representation does not match the expected format.
Example 1
Data
Date text representation on client side | 2021-06-24 |
Date text representation in SQL | 2021-Jun-24 |
Using the data above, if you have a SmartObject with a DateTime type property and search for the value 6. The second value (2021-Jun-24) will not be returned as the search is done on the SQL server.
Example 2
The following example is specific to the Auto-Complete control. When using the fields of the control in the Display Items as shown below, for example [Name]-[Surname], the behavior is different when enabling or disabling the Live Search property of the control.
- Live Search disabled: All the rows of the SmartObject are retrieved, then for each row the calculated value of the display items is determined, and the search term is matched against this.
- Live Search enabled: A filter is built for each field used in the display items, and only those rows are retrieved. For example, WHERE Name like "%search%" OR Surname like "%search%". Then these rows are filtered again based on the calculated value of the display items.
Search | Expected match | Live Search | Result |
Bob-Maggio | Bob-Maggio | Disabled | Correct |
Bob-Maggio | Enabled | Does not return the expected results | |
Bob | Bob | Enabled | Correct |
Maggio | Maggio | Enabled | Correct |
6 | 2021-06-24 | Disabled | Correct |
2021-06-24 (SQL: 2021-Jun-24) | Enabled | Does not return the expected results | |
6 | 2021-06-24 | Disabled | Correct |
2021-06-24 (SQL: 2021-06-24) | Enabled | Correct |
Example 3
DateTime is stored in server time and depending on your timezone, the Day or Time may also not filter as expected. See the example below.
Timezones
Client | Australian Central Standard Time Adelaide (GMT+9:30) |
Server | UTC (0:00) |
Data
Date on client side | 2021-06-24 00:00 |
Date in SQL | 2021-Jun-23 14:30 |
When searching 24 for example, the expected results are not returned as the search is done on the server (SQL).
Resolution
Use the Date or Time type properties in your SmartObject to return the correct results when searching by text or use date range searches instead. One example is to create a filter using rules to set up specific date ranges as shown below.