Date Range Query Fails on Joined Tables

[MikhaylaB]

I have a point layer and a standalone table that I have joined based on their GlobalID/GUID field. I need to 'select by attributes' to pull out a date range for a report, but it fails with an Invalid SQL Expression error.

If I use the same expression on the point layer with no join, it works great. I've also tried doing this with a python/sql script outside of Pro, and I have the same failed results. I can query for anything except date range. 

Is there any SQL syntax or rule that I would be violating by attempting this query on a joined table? Any ideas why this fails but other queries do not?

Enterprise 11.3/Pro 3.3.2

[Robert_LeClair · Accepted solution]

This sounds like BUG-000158475 - "Unable to use Select By Attributes on date field types with a joined Excel sheet and a table or feature class from an enterprise geodatabase."  It's in the Product Plan to be fixed but I do not know when.

It was observed initially using SQL Server 2019, Oracle 19c and ArcGIS Pro 3.1.1.  Interesting enough the queries work in SSMS but not ArcGIS Pro.

[MikhaylaB]

Thanks for the info; at least I have something I can tell the department requesting the report.

[MichaelVolz]

Since it looks like you are hitting a bug, can you create a new feature class with all needed fields including date fields and then create a model or python script to truncate and append new records to this feature class.  I would recommend a workaround at this point as you have no idea when ESRI will fix this bug so you have control with a workaround.

[Kevin_MacLeod]

June 2025 - can confirm this bug is still here.  File gdb FC with a date field; joined to SDE layer, and adding a query on the date field ...  fails.  MS SQL Server, SDE 10.91,  and the latest  Pro 3.5.  

Interestingly, I can Sort the field. Going to use that to answer my question for today... but hopfeully they fix this bug, seems like 1) it affects almost every potential customer 2) must be simple to fix

[Robert_LeClair]

I checked the BUG-000158475 status and it's still listed as "Under Consideration" for status.  You can escalate a bug by following the workflow listed here - How To: Escalate a Bug in My Esri

[MichaelVolz]

Are the date fields of the same precision between the feature class and the joined table as ESRI introduced high precision date fields with Pro 3.2 which I know has caused me issues when bringing in views from non-SDE SQL Server dbs?

[Robert_LeClair]

To @MichaelVolz point, you can learn about the new date fields introduced at ArcGIS Pro 3.2 here - What's new from the geodatabase team in ArcGIS Pro 3.2