Definition Query Date Database Table Julianday

Question

I want to use a definition query for a database table (GPKG) on a date field. I got dates from 2019 to - more or less - present day and the definition qeury should result in all data after a certain date.

When I use the "definition query builder",
"Where date is less than 14.06.2023" (german date order) and switch to SQL I can see

main.%inspection.date < JULIANDAY('2023-06-14')

but I get an error message:

"There was an error with the expression."

What's wrong? How do I use a denition query on a date correctly?

MoritzSchilling · Answer

"date" seems to be a keyword in SQL? Does that mean, you can't use a definition query on tables/fields named "date"? That would be a major problem, as a "date" field seems to be like an everyday thing in databases/tables. Isn't there any command to ignore the "date" keyword in such cases?

MoritzSchilling · Answer

No, actually the field name is just "date". The table name is "inspection" and it is named "main.inspection" by ArcGIS Pro (in QGIS I see only "inspection").

addition:

You can see "main.cities" as an example on this page:

https://pro.arcgis.com/en/pro-app/latest/help/data/databases/work-with-sqlite-databases-in-arcgis-pro.htm

Seems to be the way ArcGIS Pro names tables of SQLite databases or GeoPackages.

bixb0012 · Answer

Does your field name have a period in it?  For example, it looks like the field name is "inspection.date", which will definitely cause issues since that would make for 010085: <value> is an invalid field name.—ArcGIS Pro | Documentation

* Field names should only contain characters a–z, 0–9, and underscores.Do not use spaces, hyphens, parentheses, brackets, and other special characters, such as $, %, and #.

* Avoid using field names that contain words that are typically considered reserved keywords. These are typically data, day, month, table, text, user, when, where, year, and zone.Note that each underlying DBMS can have its own set of reserved keywords.