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.
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.
|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.
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.
|Search||Expected match||Live Search||Result|
|Bob-Maggio||Enabled||Does not return the expected results|
|Enabled||Does not return the expected results|
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.
|Australian Central Standard Time
|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).
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.