topic Re: Definition Query Date Database Table Julianday in ArcGIS Pro Questions

Definition Query Date Database Table Julianday

MoritzSchilling — Fri, 23 Aug 2024 10:50:32 GMT

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?

Re: Definition Query Date Database Table Julianday

AMoody — Fri, 23 Aug 2024 14:20:54 GMT

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

Re: Definition Query Date Database Table Julianday

MoritzSchilling — Mon, 26 Aug 2024 06:07:56 GMT

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

Re: Definition Query Date Database Table Julianday

JoshuaBixby — Mon, 26 Aug 2024 15:21:35 GMT

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.

Re: Definition Query Date Database Table Julianday

MoritzSchilling — Tue, 27 Aug 2024 05:31:57 GMT

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.

Re: Definition Query Date Database Table Julianday

MoritzSchilling — Thu, 14 Nov 2024 14:02:05 GMT

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