topic Re: EXTRACT(YEAR FROM...) query not working on SDE database in Data Management Questions

EXTRACT(YEAR FROM...) query not working on SDE database

MKF62 — Mon, 09 Apr 2018 12:19:07 GMT

For some reason this query works on FGDB but not SDE geodatabases. Is that by design? When I use select by attributes and type this query:

EXTRACT(YEAR FROM MyDateField) = 2015

This will work on a file geodatabase. When I try the same query with an SDE geodatabase, I get the error:

"There was an error executing the query. Invalid SQL syntax [[Microsoft][ODBC Driver 13 for SQL Server][SQL Server] Incorrect syntax near the keyword 'FROM'.][mydata.dbo.database]."

I have tried rewriting the query in these ways but all come back with the same error:

EXTRACT(YEAR FROM "MyDateField") = 2015

EXTRACT(YEAR FROM MyDateField) = '2015'

EXTRACT(YEAR FROM [MyDateField]) = 2015

The last thing I tried was the DatePart function. This appears to work on SDE geodatabases:

DatePart("yyyy", MyDateField) = 2015

I am confused as to why this one works but not the EXTRACT one, especially because DatePart looks like it's meant for personal geodatabases: How To: Search for specific parts of a date stored in a Date-Time field

Additionally, when building a query to use as a filter in Javascript API, the EXTRACT(YEAR FROM "MyDateField") = 2015 works on the SDE data and the DatePart does not. You'd think that one of these types of queries would work in all cases...frustrating.

Re: EXTRACT(YEAR FROM...) query not working on SDE database

KevinDunlop — Mon, 09 Apr 2018 13:44:31 GMT

This is because ArcGIS uses the database syntax to handle where clauses. Every database is slightly different with different functions. Something that works in Oracle may not working SQL Server. Something in a File GDB may not work in Postgres. And so on. The document you reference refers to this:

Other databases
Please consult the database's function documentation and syntax.
For example, SQL server uses the same DATEPART function as personal geodatabases, but with slightly different syntax:

So you need to look up the proper way to query a year for your database type. Which database are you working with?

Re: EXTRACT(YEAR FROM...) query not working on SDE database

MKF62 — Mon, 09 Apr 2018 14:16:01 GMT

Ah, that would explain why DatePart worked well for my SDE geodatabase in ArcMap (it's an SQL Server database), although I would say the documentation is not particularly correct as I was able to get DatePart to work on my database without the "special" SQL Server syntax they have listed.

Still does not explain why the use case switches when trying to perform this query in the Javascript API though. EXTRACT(YEAR FROM...) works on the database while DatePart does not inside the Javascript API.