SQL Server Definition Query on dates

ejrcarson

Hi,

Trying to get a definition query for SQL Server that works in ArcMap to work for ArcGIS Pro.

The query is checking the date field is in the current month.

YEAR(CAST(DATE_VAL AS DATE)) = YEAR(CAST(GETDATE() AS DATE)) AND MONTH(CAST(DATE_VAL AS DATE)) = MONTH(CAST(GETDATE() AS DATE))

This query doesn't work in Pro if there are no results to return, if I add a record within the date range it works.

Any ideas on why this is occurring or query solution that would work if there a no records to return?

Many Thanks,

Elliott

ejrcarson

It appears that the cause for this error is internal and results from importing a mxd to aprx. The mxd connection was to oracle, changed to sql via a script.

Adding the data as a new feature class (SQL Server) and then applying the definition query worked no problem.