Hi everyone, I’m trying to set up automated reports using Dolibarr’s REST API, but I’m encountering difficulties with SQL filters. Every time I attempt to apply a date filter using Unix timestamps, I receive a “400 - Bad Request: Error when validating parameter sqlfilters → Bad syntax of the search string, filter criteria is invalidated” error. I have followed the guidelines in the documentation and forums, but I keep getting the same error. I have also verified that the Unix timestamps are correct. For example, the timestamp 1704067200 corresponds to January 1, 2024. I have tried making the request both via cURL and through Dolibarr’s API Explorer, but the result is always the same. It seems there is an issue with the filter syntax or the API configuration.
Does anyone have experience using SQL filters in Dolibarr’s REST API? What is the correct syntax for applying filters on date fields using Unix timestamps?
Thanks in advance for your help!
Sorry, my experience with dates and SQL filter sucks too, I don’t think I ever found a good solution.
I am not sure the input should be Unix timestamps at all, perhaps try with different methods, perhaps start with ISO date?
For products you can use the following sqlfilter:
(t.datec:>=:'2022-11-13' and t.datec <'2022-11-16')
Syntax described in ~/htdocs/core/lib/functions.lib.php
→ function forgeSQLFromUniversalSearchCriteria(…)
// Must be (aaa:bbb:…) with aaa is a field name (with alias or not) and bbb is one of this operator ‘=’, ‘<’, ‘>’, ‘<=’, ‘>=’, ‘!=’, ‘in’, ‘notin’, ‘like’, ‘notlike’, ‘is’, ‘isnot’
Based on your post I tried using this sqlfilter
(t.tosell:=:1 and t.label:like:‘%member%’)
but I get all products which are for sale, but writing the sqlfilter slightly different worked 
(t.label:like:‘%member%’) and (t.tosell:=:1)
now I only get 2 products, namely those which is for sale and which contains member in the label
