Search formula not working on Linux machine

Question

I have a strange behavior: an agent called via an AJAX request should search documents to display in a calendar. For that reason I compute a search formula and then run the search method of my database in Lotusscript. This is the formula:

form="mholiday" | form="mserviceevent" | (form="mereignis" & co_status!="9") & @texttotime(@text(startdatetime)) >= [29.09.2014] & @texttotime(@text(enddatetime)) =< [10.11.2014]

Everything's fine on Domino on Windows but fails with "formula error" on a Linux machine. Am I missing something? If I omit the term with the dates everything is fine, so this is the part that causes the error.

Answer 1

Try it with @ToTime() and @Date() instead. That might help to get away from local settings' influence on server:

...  & @ToTime(startdatetime) >= @Date(2014; 9; 29) & ...

@ToTime() doesn't convert the field if it's a date time value already.

@Date doesn't depend on local settings whereas [29.09.2014] probably does.

Answer 2

I don't think it's a Linux problem, I think it's a data problem. It sounds like either a date format problem or a problem with the UNK table, used by full text search.

If the first document created on that server that had a field called "startdatetime" had a text value, then any search expects "startdatetime" to be a text value, even if there is another field in the database called startdatetime that is a date or the startdatetime field is subsequently changed to be a date. To confirm this, you can use the search bar and select the field. The operators it offers will confirm if it's expecting a date or a text value. See this answer for details on how to resolve "Query is not understandable" - Full text searching where field types have changed.

Alternatively, it may be a problem with the date format, as Knut says. In which case a test for 9/9/2014 would work but 29/9/2014 wouldn't.