Invalid SQL-Statement error when querying file-geodatabase table date field with arcobjects

Question

I want to query a DateTime Field in a table in a file-geodatabase and I keep on getting the error "invalid sql statement" (Error Code: -2147220985) while that sql statement does not give me an error on an empty table with the same configuration on an sde (even though there is no data in that table I think if the sql statement was wrong it should produce the same error there).

I use ArcObjects .Net SDK 10. I construct my whereclause like this, passing in two DateTime Objects as arguments. I've tried various ways of formating them.

string whereClause = string.Format("dat >= '{0}' AND dat <= '{1}'", fromDate.ToShortDateString(), toDate.ToShortDateString());

I then create an IQueryDef on a file-geodatabase workspace and set the previous whereclause. I then call the Evaluate() method of the IQueryDef and that is where it crashes. I was able to successfully launch other where-clauses on that same table in that same workspace but without taking the Date-Field into account. At the moment I don't see where the problem is. Is there a difference between accessing Date columns on a file-geodatabase vs a SDE with SQL out of ArcObjects? Or is something wrong with the way I use SQL here?

Answer 1

There is a differences between ArcSDE and File GDB SQL query syntax for Date fields:

SQL reference for query expressions used in ArcGIS (10.2)(Dates and time section)

http://resources.arcgis.com/en/help/main/10.2/index.html#//00s500000033000000

It appears you need to encapsulate your column names in double quotes and precede your values with date. I believe the File GDB workspace is case-sensitive as well:

This is the File GDB SQL syntax:

"Datefield" = date 'yyyy-mm-dd'

So yours might look something like:

string whereClause = string.Format(""dat" >= date '{0}' AND "dat" <= date '{1}'", fromDate.ToShortDateString(), toDate.ToShortDateString());

You can check if this is correct using the ISQLSyntax interface:

ISQLSyntax Interface

http://help.arcgis.com/en/sdk/10.0/arcobjects_net/componenthelp/index.html#/ISQLSyntax_Interface/002500000850000000/

I was also referring to this page for reference:

IQueryDef.WhereClause Property

http://resources.arcgis.com/en/help/arcobjects-net/componenthelp/index.html#//0025000006m1000000