3

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?

I am using a file geodatabase. The where clause is below...

fcMaster = arcpy.GetParameterAsText(2)

whereClause = """{0} BETWEEN date '{1}' AND date '{2}'""".format(arcpy.AddFieldDelimiters(fcMaster, "date"), dateDateFilter1, dateDateFilter2)

In response to Midavalo, I am reading start and end dates from user input, assigning those values to a variable and then using those variables to search the table for records whose dates are between the start and end dates.

In response to EvilGenius, the formats do not seem to match, but I can't figure out the source of the difference. When I use this code, with messages included for debugging...

dateFilter1 = arcpy.GetParameterAsText(0)
dateFilter2 = arcpy.GetParameterAsText(1)
arcpy.AddMessage("dateFilter1 = " + dateFilter1)
arcpy.AddMessage(type(dateFilter1))
arcpy.AddMessage("dateFilter2 = " + dateFilter2)
#convert date inputs from string to date format
dateDateFilter1 = datetime.strptime (dateFilter1, '%m/%d/%Y')
dateDateFilter2 = datetime.strptime (dateFilter2, '%m/%d/%Y')
arcpy.AddMessage("dateDateFilter1 = ")
arcpy.AddMessage(dateDateFilter1)
arcpy.AddMessage(type(dateDateFilter1))
arcpy.AddMessage("dateDateFilter2 = ")
arcpy.AddMessage(dateDateFilter2)
print (arcpy.GetMessages())

I get this output from the Progress / Results window...

Running script DailyReports... dateFilter1 = 10/03/2016 <type 'unicode'> dateFilter2 = 10/05/2016 dateDateFilter1 =  03/10/2016

dateDateFilter2 = 05/10/2016 Completed script DailyReports...

I also notice the following when the script tool dialog opens:

  1. I select the start date from the date picker / calendar (eg. Oct. 6, 2016).
  2. This appears in the dialog as 10/06/2016.
  3. I return to the date picker / calendar (eg. I change my mind about the start date and want to enter a different date).
  4. I notice that the calendar now defaults to what should be my initial date entry (Oct. 6, 2016), but is actually June 10, 2016 (06/10/2016).
  5. It seems then, that the date format is being changed and the day/month is transposed before the process even reaches my feature class / attribute table.

I have included an image to illustrate.

Date Format Change

I tried the GetParameter method, which allowed me to skip the text to date conversion (I tried this before but got an error - must have done something wrong). This worked but did not resolve the date format issue. To re-state and simplify (I think) the issue. I am trying to select all records with a date between Oct. 3 and Oct. 5. I select these dates form the date picker / calendar. There is no ambiguity regarding date formats here as it presents me with an actual calendar with months and days clearly identifiable. Though there are several records in the table between these dates, no records are selected. However, if I select records between March 10 and May 10, transposing the month and day values, the records between Oct 3 and Oct 5 are selected.

I have figured out a workaround. I went back to using the GetParameterAsText command and then converted the text to a date using the datetime.strptime command. Then I changed the data type to string in the Parameters tab of the Script Tool Properties dialog. This requires that the user type in the date in the appropriate boxes of the Script Tool dialog, in the same format as they appear in the attribute table (in our case dd/mm/yyyy). Although not quite as convenient as using the date picker / calendar, it produces the correct output. I'll let you know if I find anything else on the date format issue.

closed as off-topic by PolyGeo Oct 27 '16 at 22:23

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "This problem cannot or can no longer be reproduced. Changes to the system or to the asker's circumstances may have rendered the question obsolete, or the question does not include a procedure to enable potential answerers to reproduce the same symptoms. Such questions are off-topic as they are unlikely to help future readers, but editing them to include more details can lead to re-opening." – PolyGeo
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    What database/storage mechanism are you using? File geodatabase, MS SQL, Oracle, etc. ? You might also need to include your where clause in the question. – Richard Morgan Oct 26 '16 at 17:06
  • 1
    @KarlK Please include any new information as an edit to your question rather than in comments. Comments are intended for potential answerers to request clarification, but any response from you should be as an edit. It can be difficult to read through the comments for updates and new information to your original question. – Midavalo Oct 26 '16 at 17:42
  • 1
    Are you reading these from a table, or from user input? Your question seems to suggest both ("When I look at the attribute table") but your code only reflects user input. – Midavalo Oct 26 '16 at 18:10
  • 3
    Just to rule some things out: Do the dates printed out match what you've specified (M/D/Y)? And is the field in your data a DATE type or string? – Evil Genius Oct 26 '16 at 18:12
  • 1
    Why not try using GetParameter instead of GetParameterAsText. It will return a date object, provided your parameter is set to a Date. – Fezter Oct 27 '16 at 0:18