querry always returns ZERO results, using date type

[AliciaMein]

Hello,
Still working with a grid, trying to count the number of negative s after the first positive.
Everything seems to be working as expected, except my count is always zero.

Sample output:
[datetime.datetime(2009, 3, 24, 0, 0), datetime.datetime(2009, 3, 24, 0, 0), datetime.datetime(2010, 3, 31, 0, 0), datetime.datetime(2010, 3, 31, 0, 0), datetime.datetime(2011, 3, 22, 0, 0)]
2009-03-24 00:00:00
0
[datetime.datetime(2007, 11, 19, 0, 0)]
2007-11-19 00:00:00
0
[datetime.datetime(2008, 4, 2, 0, 0), datetime.datetime(2010, 4, 14, 0, 0)]
2008-04-02 00:00:00
0
[datetime.datetime(2005, 9, 14, 0, 0), datetime.datetime(2006, 3, 31, 0, 0), datetime.datetime(2006, 4, 12, 0, 0), datetime.datetime(2010, 4, 1, 0, 0), datetime.datetime(2010, 4, 1, 0, 0), datetime.datetime(2011, 3, 22, 0, 0), datetime.datetime(2011, 3, 22, 0, 0), datetime.datetime(2011, 3, 25, 0, 0), datetime.datetime(2011, 4, 13, 0, 0), datetime.datetime(2011, 4, 14, 0, 0)]
2005-09-14 00:00:00
0
[datetime.datetime(2011, 4, 14, 0, 0)]
2011-04-14 00:00:00
0

for grid in grids:
        dates = []
        arcpy.SelectLayerByLocation_management("cwdpts4Anlys", "WITHIN", grid.shape, "", "NEW_SELECTION")
        positives = arcpy.SearchCursor("cwdpts4Anlys",' "ELISALYMPHNODE" = \'Positive\' ' , "", "SAMPLE_DATE" )

        for positive in positives:
            dates.append(positive.SAMPLE_DATE)
        print dates
       
        earliest = min(dates)
        print earliest
        arcpy.SelectLayerByLocation_management("cwdpts4Anlys", "WITHIN", grid.shape,"", "NEW_SELECTION")
        arcpy.SelectLayerByAttribute_management("cwdpts4Anlys", "SUBSET_SELECTION", ' "ELISALYMPHNODE" = \'Negative\' ')
        arcpy.SelectLayerByAttribute_management("cwdpts4Anlys","SUBSET_SELECTION",'"SAMPLE_DATE" <= date.earliest ' )
        result = int(arcpy.GetCount_management("cwdpts4Anlys").getOutput(0))
        print result
[\Code]


I have tried several different formats in the SelectLayerByAttribute line including .....date earliest   and........ earliest, but the return is always the same.

thanks again
Alicia

[XanderBakker · Accepted solution]

Hi Alicia,

The line of code where it goes wrong is:

arcpy.SelectLayerByAttribute_management("cwdpts4Anlys","SUBSET_SELECTION",'"SAMPLE_DATE" <= date.earliest ' )

This is due to the where clause you're using. Just to be sure, you could put a hash tag "#" before that line and see if there are any results.

To create a proper where clause with a date, try this:

where = "{0} <= date '{1}'".format(arcpy.AddFieldDelimiters("cwdpts4Anlys", "SAMPLE_DATE"), earliest) arcpy.SelectLayerByAttribute_management("cwdpts4Anlys", "SUBSET_SELECTION", where)

The "arcpy.AddFieldDelimiters(datasource, fieldname)" is used to add correct field delimiters to a field name. The format syntax is used to create a correct string to be used as where clause. In this case the "{0}" will be replaced by the field name including the delimiters and {1} will be replaced by the content of the variable "earliest".

To use a date in a SQL expression, you use "date" and place the date string between single quotes.

It is a little difficult to test without any data, but this might just work. Hope it'll work for you.

Kind regards,

Xander

[AliciaMein]

Perfect!
Thank You.
I will read a little more about the formatting on my own.
Alicia