laptop and a wrench

Bug

Query Builder does not build the proper date query when the layer has a join and all records are kept. Also, manually writing the expected SQL query for dates when joins are present does not work. This issue effects all GDBs (PGDB, SQL Server, Oracle) except FGDB.

Last Published: August 25, 2014 No Product Found
Bug ID Number NIM004401
SubmittedSeptember 27, 2006
Last ModifiedApril 28, 2025
Applies toNo Product Found
Version found9.0
StatusWill Not Be Addressed

Additional Information

No Public Explanation

Workaround

Use FGDB - there are no problems with above queries when using FGDB. For other databases, include a field from the other side of the join. For example:If you are using PGDB, modify the query to be the following:left hand side of the join: FC1.date = date '01/12/2001' and Table1.OBJECTID > 0right hand side of the join: Table1.date = date '01/12/2001' and FC1.OBJECTID > 0For SQL Server:vtest.MAP.FC1.MYDATE = date '2008-10-01 12:35:41' AND vtest.MAP.Table1.OBJECTID IS not NULLFor Oracle:left side: MAP.FC1.MYDATE = date '2008-10-01 12:35:41' and MAP.table1.OBJECTID is not nullright side: MAP.TABLE1.MYDATE = date '2008-10-01 12:35:41' and MAP.FC1.OBJECTID is not nullThis is documented in the help: <a href="http://webhelp.esri.com/arcgisdesktop/9.2/index.cfm?id=257&pid=247&topicname=SQL_reference" target="_blank">http://webhelp.esri.com/arcgisdesktop/9.2/index.cfm?id=257&pid=247&topicname=SQL_reference</a>It is also documented in KB22132:<a href="http://support.esri.com/index.cfm?fa=knowledgebase.techArticles.articleShow&d=22132" target="_blank">http://support.esri.com/index.cfm?fa=knowledgebase.techArticles.articleShow&d=22132</a>Also, if the joined tabled has nulls (i.e. there were some records that didnt match), and you want to query dates on the left hand side of the join including records that dont have a match on the right hand side, then you can run this query (for PGDb):FC1.MYDATE = date '02/10/2008 8:51:59' AND (Table1.OBJECTID IS NOT NULL OR Table1.OBJECTID IS NULL)The OR statement selects all records on the right hand side of the join.This is what it would look like for SQl Server:vtest.MAP.FC1.MYDATE = date '2008-10-01 12:35:41' AND (vtest.MAP.table1.OBJECTID IS NULL or vtest.MAP.Table1.OBJECTID IS NOT NULL)This is what it would look like for Oracle:MAP.FC1.MYDATE = date '2008-10-01 12:35:41' and (MAP.Table1.OBJECTID is null or MAP.Table1.OBJECTID is not null)

Steps to Reproduce

Bug ID: NIM004401

Software:

  • No Product Found

Get notified when the status of a bug changes

Download the Esri Support App

Discover more on this topic

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options