[GIS] Use arcpy to query by date field in ArcGIS for Desktop

arcgis-10.2arcpydatetime

I have an ArcGIS table in a file geodatabase that contains a date field. I would like to write a python (arcpy) function that creates and returns a table view for a date-range given a starting and/or ending date.

Here's what I have so far:

def get_date_range_table_view(grid_table, start_date=datetime.datetime(1, 1, 1),
                              end_date=datetime.datetime(9999, 12, 31)):

    min_date = '"DateTime"' + " >= date '" + str(start_date) + "'"   
    max_date = '"DateTime"' + " <= date '" + str(end_date) + "'"
    expression = " AND ".join([min_date, max_date])

    return arcpy.MakeTableView_management(grid_table, "date_range_table_view",   
                                          expression)

The expression that uses 'start_date' works great and returns a table view that can be used with a SearchCursor. The trouble that I'm having is getting the 'end_date' logic to return a similar table view. Although the logic is identical, except for the '>=' symbol, the table view that is returned has no readable rows.

I found a similar question here:
Arcpy cursors, WHERE clauses, and date/time fields

Which directs the readers to this resource (which has information about querying Date fields in ArcGIS 10.2 based on database type):
SQL reference for query expressions used in ArcGIS

Best Answer

The above code snippet works fine. I had been testing the function with the following variable values:

# datetime.datetime(year, month, day, hour)     # function signature

start_date = datetime.datetime(2013, 1, 1, 0)
end_date = datetime.datetime(2013, 1, 31, 23)

The testing data that I'm using has date ranges from Jan 1, 2014 1:00 AM - Jan 31, 2014 11:00 PM. (Notice the difference in the year value.)

Suffice it to say that it was user error that kept this function from working.

Related Question