Why is querying against a date on left part of a join such an issue?

[BillFox]

Example:

• feature class in one enterprise geodatabase
• table to join to the feature class from a second enterprise geodatabase

Verify Expression Error: The expression was verified successfully, but no records were returned.

After adding the bit about objectid from the joined table > 0

The expression was successfully verified

Reference:

SQL reference for query expressions used in ArcGIS—Help | ArcGIS Desktop 

"Known limitations

Querying against a date on the left part (first table) of a join only works with file-based data sources, such as file geodatabases, shapefiles, and DBF tables. However, there is a possible workaround for working with non-file-based data, like personal geodatabase data and ArcSDE data as described below.

Querying against a date on the left part of a join will be successful when using the limited version of SQL developed for file-based data sources. If you are not using such a data source, you can force the expression to use this format. This can be done by making sure that the query expression involves fields from more than one join table. For example, if a feature class and a table (FC1 and Table1) are joined and are both from a personal geodatabase, the following expressions will fail or return no data:

FC1.date = date #01/12/2001# FC1.date = date '01/12/2001'

To query successfully, you can create a query as follows:

FC1.date = date '01/12/2001' and Table1.OBJECTID > 0

Since the query involves fields from both tables, the limited SQL version will be used. In this expression, Table1.OBJECTID is always > 0 for records that matched during join creation, so this expression is true for all rows that contain join matches.

To ensure that every record with FC1.date = date '01/12/2001' is selected, use the following query:

FC1.date = date '01/12/2001' and (Table1.OBJECTID IS NOT NULL OR Table1.OBJECTID IS NULL)

This query will select all records with FC1.date = date '01/12/2001', whether or not there was a join match for each particular record."

[TedKowal]

Typically a Join between two tables require a key relationship eg  part of the sql in the join should relate FC1.somekey  = Table1.somekey.  Too add some complexity Left Joins or Right Joins include all of one set and those matching of another.  This too requires an equality or relationship key which I see missing from your example.  Joining on dates within databases presents other issues.  In a database, Datefields contain not only dates but time as well.  To complicate it further, dates are stored in the Databases own default format which can differ between databases ie MS Access/SQL Server/Oracle.... so the trick is placing or setting the date in a compatible format and setting the join key on that converted format  between the FC1 dataset and Table1. 

In your examples above it appears that you are trying to do a cross join or in equality type of join and if that is the intent then search for info on Cross Joins.

Hope this gives you some ideas!

[BillFox]

Hello Ted,

This is just a normal, run-of-the-mill join of an Assessor's ownership table to a parcel layer based on the assessor's parcel number that we have used for decades.

We only stumbled on the date issue with joins while troubleshooting another project.

The same join works fine searching for text fields such as owner name but not date fields until we add in that "work-around".

It even works for dates if both the feature class and the table are in the same enterprise geodatabase.

We opened an incident with esri support to see if they have any additional details on this "known limitation"

[TedKowal]

It is not only with ESRI products..... try date join with a SQL-Server table to an Oracle table.         Can be a nightmare of workarounds.  Personally I make views setting the join fields in a common format as calculated fields and when I need to use the ESRI table join -- I join it against a "View" which is set up for a simple run of the mill esri table join this is even effective when using date ranges.

[BillFox]

The esri Bug reference here

NIM012414: Select query based on date field fails when joining .. 

[BillFox]

Even though our join is not based on a date field we see similar weirdness.

[TedKowal]

Even though I never experienced the bug....I see that the work-around is a View.  My reason for using view are to:

1. Simplify complex queries into more simplier ones (Makes debugging issues much easier)
2. Simplify Joins and Relationships
3. Cast various datatypes into more generic ones   eg  Varchar --> String or Text  (even though esri does not have issues with this one other applications outside of GIS do)

Glad you found workarounds for your issue.  I am sure someone will find this thread helpful.

[BillFox]

Same issue in Pro 2.0.1

Both ArcMap 10.5.1 and Pro 2.0.1 work using this option:

• AND (<> IS NOT NULL OR <> IS NULL)

Also,

BUG-#000110490: When querying a date field of a SDE geodatabases feature class, ArcGIS Pro's Select by Attribute tool fails to preface the date being queried with the necessary delimiter ('date').