2

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?

4

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

  • Thank you very much. Your example works. One can ommit the double quotes around the field name. I guess one would need to use an escape sequence when using your exact example since the double quotes around dat would not compile in c#. – Chris P Sep 3 '14 at 7:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.