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
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:
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?
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.