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]

Still having issues with trying to generate a dynamic valid SQL expression for current month, even if records are not returned in ArcGIS Pro 2.5.2?

Below are three examples, the first example is string based and valid but does not return a result. The second example use some functions to generate the year using the same month and day as a string and theatrically generates the same value as the first example but expression is deem invalid. The third and final example, uses the syntax from the second example and has the month reduced by 1 (this returns a record) and is considered a valid expression.

Can anyone advise me on an appropriate solution?

[ejrcarson]

I tried the second invalid expression in ArcMap and it was successful.

[ejrcarson · Accepted solution]

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.