How do I ensure accurate date selection within script tool?

KarlKeough — Wed, 26 Oct 2016 17:19:08 GMT

I have created a script tool that will ask a user to select start and end dates within which records will be selected from a feature class. The field containing the dates is formatted as type Date. I want to use those dates within an sql query to select the records, which requires that they be formatted as dates, but the GetParameterAsText command converts the user selection to a string, which means I have to convert back to date in the code. The code I am using is as follows:

#get start and end dates for report generation

dateFilter1 = arcpy.GetParameterAsText(0)

dateFilter2 = arcpy.GetParameterAsText(1)

print dateFilter1

print dateFilter2

#convert date inputs from string to date format

dateDateFilter1 = datetime.strptime (dateFilter1, '%m/%d/%Y')

dateDateFilter2 = datetime.strptime (dateFilter2, '%m/%d/%Y')

When I look at the attribute table, the date shows up in the format "30/09/2016" for Sept 30, 2016, for example, which does not match what I have in the code above. However, if I run the script tool and select dates say between Oct 2, 2016 and Oct 5, 2016 ("02/10/2016" and "05/10/2016" in the attribute table), I get the records I expect. However, if I select dates between Sept. 29, 2016 and Oct 5, 2016 ("29/09/2016" and 05/10/2016") I get the following error:

File "C:\Python27\ArcGIS10.2\Lib\_strptime.py", line 325, in _strptime

(data_string, format))

ValueError: time data '29/09/2016' does not match format '%m/%d/%Y'

This seems to suggest that the data in the date field is being read as if it was in "%d/%m/%Y" format. If I change the code so that the format for the strptime command shows "%d/%m/%Y" the script runs without error, but returns no records, which also suggests that it is reading the date in the source data as "%d/%m/%Y" (all records are in late September and early October).

Can anyone tell me what might be wrong?

Re: How do I ensure accurate date selection within script tool?

DarrenWiens2 — Wed, 26 Oct 2016 18:10:23 GMT

Just curious, if you sort the date field in the attribute table, does it sort how you would expect, or like Oct. 2, Nov. 2., Oct. 3, Nov. 3, etc.?

Re: How do I ensure accurate date selection within script tool?

KarlKeough — Wed, 26 Oct 2016 18:13:22 GMT

It sorts as I would expect, by month and day.

Re: How do I ensure accurate date selection within script tool?

DanPatterson_Retired — Wed, 26 Oct 2016 18:21:31 GMT

have you checked the query requirements, your regional settings and your data formats, to see if they match? see the date time section here

SQL reference for query expressions used in ArcGIS—Help | ArcGIS for Desktop

topic Re: How do I ensure accurate date selection within script tool? in Developers Questions

How do I ensure accurate date selection within script tool?

Re: How do I ensure accurate date selection within script tool?

Re: How do I ensure accurate date selection within script tool?

Re: How do I ensure accurate date selection within script tool?