4

I'm using ArcGIS 10.3.1 for Desktop to test before writing an answer to Entering date with arcpy.GetParameterAsText()? I have written the test code below that I am running against a test feature class (testFC) with four line features to try and update its dateField with a constant date value of "01.07.2015".

import arcpy

dmyString = "01.07.2015"
fc = r"C:\temp\test.gdb\testFC"
item = "dateField"

dmyDate = datetime.datetime.strptime(dmyString,'%d.%m.%Y')
print dmyDate

arcpy.CalculateField_management(fc,item,dmyDate,"PYTHON")

with arcpy.da.SearchCursor(fc,[item]) as cursor:
    for row in cursor:
        print row[0]

I think the above should work, and from the output below you can see that the date object (dmyDate) seems to be created fine, but the values written to each of the four features are not what I would expect.

>>> ================================ RESTART ================================
>>> 
2015-07-01 00:00:00
1899-12-30 00:00:00
1899-12-30 00:00:00
1899-12-30 00:00:00
1899-12-30 00:00:00
>>> 

Can someone explain why 1899-12-30 00:00:00 is being written instead of 2015-07-01 00:00:00?

The attribute table displays as below:

enter image description here

If I replace the CalculateField line above with either:

arcpy.CalculateField_management(fc,item,"date '2015-07-01 00:00:00'","PYTHON")

or

arcpy.CalculateField_management(fc,item,"date '2015/07/01 00:00:00'","PYTHON")

as per @Vince's comments I receive an error:

Traceback (most recent call last):
  File "C:\temp\test.py", line 11, in <module>
    arcpy.CalculateField_management(fc,item,"date '2015-07-01 00:00:00'","PYTHON")
  File "C:\Program Files (x86)\ArcGIS\Desktop10.3\ArcPy\arcpy\management.py", line 3457, in CalculateField
    raise e
ExecuteError: ERROR 000539: SyntaxError: invalid syntax (<expression>, line 1)
Failed to execute (CalculateField).
  • 1
    By chance, is there a selection on the feature class, so that only one value is updated? What values display in the attribute table? – recurvata Jul 10 '15 at 13:55
  • 2
    Since the date is mapping to a "-1" of the epoch, it seems that your datetime object is not accepted as input to the expression parameter. Have you tried using a string value, formatted "date 'YYYY-MM-DD HH:MM:SS'" or "date 'YYYY/MM/DD HH:MM:SS'"? – Vince Jul 10 '15 at 18:27
  • 1
    According to the documentation, the '1899/12/30' date is the epoch in ArcGIS, and both date '2015-07-01' and date '2015-07-01 00:00:00' should be valid with FGDB sources. Having the date formatting fail silently like that is, IMHO, a bug which should be fixed. – Vince Jul 10 '15 at 20:46
  • @Vince, that is SQL syntax..it's not supposed to work for FieldCalc (take a look at the top of the page). – Paul Jul 11 '15 at 0:37
3

The code below demonstrates how to get this to work using code very similar to that in the question, but I recommend also reviewing the detailed explanation that follows in order to understand why it works:

import arcpy

dmyString = "01.07.2015"
d,m,Y = dmyString.split(".")
dmyDate = datetime.datetime(int(Y),int(m),int(d))
epoch = datetime.datetime(1899, 12, 30)
days = (dmyDate - epoch).days

fc = r"C:\temp\test.gdb\testFC"
item = "dateField"

print dmyDate

arcpy.CalculateField_management(fc,item,days,"PYTHON")

print "\nOutput:"
with arcpy.da.SearchCursor(fc,[item]) as cursor:
    for row in cursor:
        print row[0]

produces:

>>> ================================ RESTART ================================
>>> 
2015-07-01 00:00:00

Output:
2015-07-01 00:00:00
2015-07-01 00:00:00
2015-07-01 00:00:00
2015-07-01 00:00:00
>>>

enter image description here

Don't be disturbed that you see 1/07/2015 (instead of 7/01/2015) above - PolyGeo's Windows settings are for Australia where they tend to use dd/mm/YYYY.

Why it works:

Alright, so CalculateField is expecting a the number of days since the epoch (1899-12-30 as @Vince pointed out) whereas UpdateCursor accepts either a datetime.datetime() object or delta days.

import arcpy, time
arcpy.env.workspace = r"C:\Users\Paul\Desktop\testing.gdb"

def compare(calc, cursor):
    #Create new fields
    arcpy.DeleteField_management("dater", ["date1", "date2"])
    arcpy.AddField_management("dater", "date1", "DATE")
    arcpy.AddField_management("dater", "date2", "DATE")

    #Let's try CalcField
    arcpy.CalculateField_management("dater", "date1", calc, "PYTHON")

    #And now Ucursor
    with arcpy.da.UpdateCursor("dater", ["date2"]) as rows:
        for row in rows:
            rows.updateRow([cursor])

    #Let's take a look.....
    with arcpy.da.SearchCursor("dater", ["date1", "date2"]) as rows:
        for row in rows:        
            print("Calc:\t{}\tUcursor:\t{}".format(*row))


date1 = datetime.datetime(2015, 7, 10)
#Days since the epoch            
epoch = datetime.datetime(1899, 12, 30)
days = (date1 - epoch).days

print("Datetime to both:\n")
compare(date1, date1)
print("\nDelta days to calc, datetime to Ucursor:\n")
compare(days, date1)
print("\nDelta days to both::\n")
compare(days, days)

The Result:

Datetime to both:

Calc:   1899-12-30 00:00:00 Ucursor:    2015-07-10 00:00:00
Calc:   1899-12-30 00:00:00 Ucursor:    2015-07-10 00:00:00
Calc:   1899-12-30 00:00:00 Ucursor:    2015-07-10 00:00:00

Delta days to calc, datetime to Ucursor:

Calc:   2015-07-10 00:00:00 Ucursor:    2015-07-10 00:00:00
Calc:   2015-07-10 00:00:00 Ucursor:    2015-07-10 00:00:00
Calc:   2015-07-10 00:00:00 Ucursor:    2015-07-10 00:00:00

Delta days to both::

Calc:   2015-07-10 00:00:00 Ucursor:    2015-07-10 00:00:00
Calc:   2015-07-10 00:00:00 Ucursor:    2015-07-10 00:00:00
Calc:   2015-07-10 00:00:00 Ucursor:    2015-07-10 00:00:00

Very interesting stuff! Clearly, cursors are the preferred choice when dealing with dates. That or the VBScript parser for FieldCalc.

  • Thanks, but that's a workaround that I know works, because I normally use cursors to do this. It was the question I linked to, and this duplicate of that that led my to trying to run a test to demonstrate how to do it with CalculateField. +1 for some useful code that I am sure future visitors will benefit from. – PolyGeo Jul 11 '15 at 0:20
  • 1
    @PolyGeo, if you really want to scratch your head, try entering time.strftime("%Y/%m/%d") into FieldCalculator...it returns 1900-01-27 00:00:00 interestingly enough. – Paul Jul 11 '15 at 0:31
  • 1
    @PolyGeo, the "random" 1900 date is what helped me figure it out. I started passing incrementing numbers to FieldCalc and watched the date increase. – Paul Jul 11 '15 at 1:34
  • That's awesome Paul - I hope you don't mind that I added in the test code I used to verify what you say into your answer. I think this Q&A will be visited by people who may not be able to go from your "proof" to getting their code working. I was worried that my question may have indicated something obvious that I was overlooking and very glad to have you, @Vince and recurvata take part in the challenge of how to answer it. – PolyGeo Jul 11 '15 at 2:24
  • 1
    @PolyGeo, by all means! I edited yours slightly so that it's a bit simpler to see the difference between dmyDate and the output from the cursor. – Paul Jul 11 '15 at 2:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.