Confused by date field query

Question

I've only been messing around with Python for a few months and am having trouble understanding how to script for a date field.  Does this require the dateTime functions or just a correctly built SQL query??

Specifically I have a shapefile with a date field that I would like to be able to pull all records (or at least get a count of all records) that were worked on within a fiscal year.  So to be able to look at Oct 1, 2011 to Sept 30, 2012.

I've been looking through the ArcGIS 10.0 help topics and can't seem to find specifics other than the the format of the "datefield" = 'yyyy-mm-dd'.

Thanks for any pushes in the right direction or help topics!

KendraRodgers · Answer

Actually that code isn't working, or not correctly - its selecting everything with an entry in those field names.  If I figure it out, I'll post back here my successful code.

KendraRodgers · Answer

Hi Menno, Thank you for your help. I ended up working out a scipt like this: Not sure this is the prettiest thing, but it seems to be working good! Kendra l_reeval = ' "L_Reeval" >= date \'2011-10-01\' AND "L_Reeval" <= date \'2012-09-30\' ' yr_rec = ' "YR_Rec" >= date \'2011-10-01\' AND "YR_Rec" <= date \'2012-09-30\' ' fyRows = arcpy.UpdateCursor(in_table) fy = fyRows.next() while fy: l_reeval = fy.getValue("L_Reeval") yr_rec = fy.getValue("YR_Rec") if l_reeval: num_fiscal_year_sites += 1 elif yr_rec: num_fiscal_year_sites += 1 else: pass fyRows.updateRow(fy) fy = fyRows.next() *Pardon the indent mistakes...haven't quite figured out how to paste in code correctly.

MennoNijhuis1 · Answer

Hi Kendra,

I found that date SQL queries in ArcGIS behave quite different when selecting dates in geodatabases and shapefiles, which might explain your confusion.

The different SQL query formats that should be used with date fields:

file geodatabase:' "date" = #12-04-2009# '

personal geodatabase:' [date] = #12-04-2009# '

shapefile:' "date" = date \'2009-04-12\' '

For geodatabase tables it's also possible to query on a specific datetime using for instance 
#12-04-2009 12:00:00#

Regards,
Menno