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)
#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
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?
have you checked the query requirements, your regional settings and your data formats, to see if they match? see the date time section here