whereClause in SearchCursor issue in ArcMap 10.6

[MohanPalleti]

I am having a problem with whereClause in SearchCursor. Can anyone tell me what is the issue here?

When I use the first version without date everything works fine. When I add the date part. It fails with the error messages I pasted at the bottom:

>>

whereClause =   'STA_CODE = "' + code + '" AND FC IS NOT NULL AND FC <> 0'

whereClause =  'SAMP_DATE < "2020/12/31" AND STA_CODE = "' + code + '" AND FC IS NOT NULL AND FC <> 0'

recs = arcpy.SearchCursor(samples, whereClause, "", "", "SAMP_DATE D")

>>

>>>>>------

Failed script ScriptMP...

Traceback (most recent call last):
File "C:\Users\mpalleti\Documents\PMR\SamplingStatistics\test6E.py", line 47, in <module>
recs = arcpy.SearchCursor(samples, whereClause, "", "", "SAMP_DATE D")
File "c:\program files (x86)\arcgis\desktop10.6\arcpy\arcpy\__init__.py", line 1186, in SearchCursor
return gp.searchCursor(dataset, where_clause, spatial_reference, fields, sort_fields)
File "c:\program files (x86)\arcgis\desktop10.6\arcpy\arcpy\geoprocessing\_base.py", line 361, in searchCursor
self._gp.SearchCursor(*gp_fixargs(args, True)))
RuntimeError: ERROR 999999: Error executing function.
An invalid SQL statement was used.
An invalid SQL statement was used.

Failed to execute (ScriptMP).

[Anonymous User · Accepted solution]

Hi Mohan Palleti‌,

Querying dates is bit of a special case, in that the correct way to format the where clause is dependent upon the flavour of SQL used in the database containing our data - and often whether on not the field values specify both date AND time, or only the date. Generally speaking, if the field values include a both date and time - then we'll want to provide both in our where clause expression, while only the date need be specified if that's all our field values contain. 

So for example if we're working with a SQL Server database where both date and time are specified in the field values, the syntax for your where_clause expression would be 

whereClause =  "SAMP_DATE < '2020-12-31 23:59:59' AND STA_CODE = '" + code + "' AND FC IS NOT NULL AND FC <> 0"‍‍‍

Whereas the same expression for a File Geodatabase - which uses a slightly different flavour of SQL - the correct expression syntax would be formatted as 

whereClause =  "SAMP_DATE < date '2020-12-31 23:59:59' AND STA_CODE = '" + code + "' AND FC IS NOT NULL AND FC <> 0"‍‍‍‍

For a complete reference on how to format date and time expressions in SQL / where_clause expressions for all supported databases, check out SQL reference for query expressions used in ArcGIS—ArcMap | Documentation 

Hope that helps!

Cheers,

James

[MohanPalleti]

Thank you James!

Your suggestion 'date' worked! I was completely lost to fix this.

Mohan Palleti