Why is arcpy.CalculateField_management() writing 1899-12-30 00:00:00 instead of expected date?

Question

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:

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).

Answer 1

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
>>>

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.