AnsweredAssumed Answered

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

Question asked by MollyKFoley on Apr 9, 2018
Latest reply on Apr 9, 2018 by MollyKFoley

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.