ArcGIS-Desktop – Definition Query for Current Date in SQL Server Native Syntax Error 10.2

arcgis-10.2arcgis-desktopdefinition-querysql server

I have this definition query that worked for a layer from a file geodatabase. Now the same query will not work from the same layer in an SDE. I am getting a 'SQL Server Native Client Syntax' error. Any thoughts?

ExprDate <= (CURRENT_DATE + 30) AND ExprDate >= CURRENT_DATE

Screenshot

Best Answer

Try it with getdate()

ExprDate <= (GETDATE() + 30) AND ExprDate >= GETDATE()

Related Solutions

ArcMap – Constructing a Definition Query for Current Month Features

Based on this, you can also do:

EXTRACT(MONTH from DateField) = EXTRACT(MONTH from CURRENT_DATE) AND EXTRACT(YEAR from DateField) = EXTRACT(YEAR from CURRENT_DATE)

Or a shorter solution that would also work is:

DateField >= CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)

[GIS] ArcGIS 10.2 Query Layer on SQL Server performance

As you stated, your query seems to execute swiftly on the database level. Even if you were able to make the SQL more efficient, the real performance is on the spatial level.

Spatial SQL statements, like the one you are using, were allowed only recently with the introduction of the geometry type. SQL Server 2008 for ArcSDE supports three geometry data type, SDEBINARY, GEOMETRY and GEOGRAPHY. The differences are listed here

For best performance make sure you are using the Geometry or Geography (not SDEBINARY though since it is obsolete and not recommended) based on the nature of your data whether you are using earth spatial reference or not. Also make sure to rebuild spatial index on the TOWNLANDS featureclass. You can do that from ArcCatalog by right clicking on the featureclass, properties and select the indexes tab.

Hope that helps.

Best Answer

Related Solutions

ArcMap – Constructing a Definition Query for Current Month Features

[GIS] ArcGIS 10.2 Query Layer on SQL Server performance

Related Question