Definition Query Date Database Table Julianday

[MoritzSchilling]

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?

[AMoody]

Not sure if you can see the option, but when I use def queries related to date fields it breaks up something like:

"Where [Date Field] is before [Selected Date]" 

switching to SQL it looks like:

DATEFIELD < timestamp '2024-07-23 13:02:00'  (for this example my date field had a timestamp)

Maybe this will help more too: https://pro.arcgis.com/en/pro-app/latest/help/data/tables/date-fields.htm

[MoritzSchilling]

Thanks for your reply, but I can't get it to work. I attached three screenshots to illustrate my problem. I just tried the definition query in QGIS on the same GeoPackage table and it works with "date" > '2023-12-30'.

[JoshuaBixby]

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.

---

[MoritzSchilling]

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

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

[MoritzSchilling]

"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?