Filtering active date between two date fields using file geodatabase in QGIS

Question

I would like to save a .qlr with data stored in a file geodatabase that displays active records that fall between two DateTime fields: Start date field and End Date field (inclusive). I had this query working in ArcGIS Pro, but would like to figure it out in QGIS.

In ArcGIS Pro, the definition query was: ((CURRENT_DATE() BETWEEN Start_Date_Debut AND End_Date_Fin) OR (End_Date_Fin IS NULL)) AND (Frequency_Frequence = 3)

Edit: Using the suggestions below I have tried breaking down and using a simple query QGIS's Query Builder:

now() > "start_date_debut"

I am still receiving an error when executing the query:

An error occurred when executing the query.
The data provider said:
OGR[3] error 1: SQL Expression Parsing Error: syntax error, unexpected 
')'. Occurred around :
now() > "start_date_debut"
  ^

There are some null values in the end date fields (because there is no end date associated with end date).

Answer 1

If you are talking about the Filter (aka Query Builder) part of QGIS - also the same as the 'Provider Feature Filter' under Layer Properties > Source, but more easily accessed by right clicking the layer in the layer list and selecting 'Filter' ...

It uses some weird flavour of SQL combined with some QGIS expression syntax. It doesn't recognise the variable CURRENT_DATE() nor the function BETWEEN.

However, I did get this to work for the first segment of your query.

now() > "start_date_debut" AND now() < "end_date_fin"

Example (today is 2022-05-30)

Original - note field type is date, not string

Filter query -

Results

Note if you decide to use DB Manager / Virtual Layers to generate a filter it explicitly uses Spatialite syntax (based on SQLite - check your QGIS version to see which version).