1

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).

2
  • I want to apply this filter to the layer properties (which I'm hoping would affect the attribute table in turn).
    – kart
    May 27 at 15:26
  • Standard advice: see if smaller portions of the query, such as CURRENT_DATE() BETWEEN Start_Date_Debut AND End_Date_Fin work? May 28 at 12:58

1 Answer 1

0

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

enter image description here

Filter query -

enter image description here

Results

enter image description here

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).

3
  • Thanks for the help. This didn't work for me even just trying with "now() > "start_date_debut" " Wondering if it is because the data is stored in a file geodatabase which I have to leave it in. It is in datetime data type. thanks.
    – kart
    Jun 1 at 2:02
  • @kart Fyi: the syntax as well as possibilities of the feature filter depend on the feature provider. E.g. if the datasource is a PostGIS db, you can use all options your db provides in the same syntax. If the datasource is a geopackage you can use all SQLite commands.
    – MrXsquared
    Sep 28 at 21:34
  • thanks - this data is stored in a file geodatabase and the SQL expression/syntax does result in an error unfortunately (even though runs successfully in ArcGIS). Could this just be a limitation of using an esri file geodatabase in QGIS (I can not store this data elsewhere).
    – kart
    Sep 28 at 21:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.