topic Re: Confused by date field query in Python Questions

Confused by date field query

KendraRodgers — Tue, 13 Dec 2011 04:27:34 GMT

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!

Re: Confused by date field query

MennoNijhuis1 — Tue, 13 Dec 2011 13:31:20 GMT

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

Re: Confused by date field query

KendraRodgers — Wed, 14 Dec 2011 04:40:15 GMT

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.

Re: Confused by date field query

KendraRodgers — Thu, 15 Dec 2011 02:37:47 GMT

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.