Calculate new field by adding 6 hours to date field error on midnight values

[AndrewL]

Hello. I have the following code that works fine when there is not a record with a date/time of midnight. However today I came across a record at a time of midnight and it breaks the script. Is there a way around this? Thank you. I have provided a screenshot of the attributes. Both fields are date fields.

Error: ValueError: time data '12/16/2019' does not match format '%m/%d/%Y %I:%M:%S %p'

# Add CST date/time field
print ("Adding CST date field")
arcpy.AddField_management(mergedFCSquaresDis, "DATE_CST", "Date")
expression = "datetime.strptime(!DATE_UTC!,'%m/%d/%Y %I:%M:%S %p') + timedelta(hours=-6)"
arcpy.CalculateField_management(mergedFCSquaresDis, "DATE_CST", expression, "PYTHON_9.3")‍‍‍‍‍

[Anonymous User]

Hi Andrew,

<Edit: I just noticed that in your script you are subtracting 6 hours rather than adding as the heading states, the script below reflects the script as posted>

The issue seams to be that you are trying to force a format on to a field that does not have the information that you are asking for.

If we remove the formatting from the equation, we can apply it after if needed, we can do the calculation on the base format.

I haven't had a chance to test this yet, but by the reading that i have done it should work.

----# Add CST date/time field
print ("Adding CST date field")
arcpy.AddField_management(mergedFCSquaresDis, "DATE_CST", "Date")
expression = "!DATE_UTC! - 0.25"
arcpy.CalculateField_management(mergedFCSquaresDis, "DATE_CST", expression, "PYTHON_9.3")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Since the datetime fields store date values as the number of "Number of Days since 1/1/1900", see the tips at the bottom of the page of:
Making simple date field calculations using the field calculator—Help | ArcGIS Desktop 

simply subtracting 6 hours (which works out at 0.25 of a day) from the DATE_UTC time should give you the result you are looking for.

Here is a handy resource for dealing with Datetime in Python:
8.1. datetime — Basic date and time types — Python 2.7.17 documentation

I hope that this helps

Michael

[AndrewL]

Thanks Michael. I tried that but I get error: TypeError: unsupported operand type(s) for -: 'str' and 'float'

I double checked the field type for DATE_UTC and it is a Date field. I am guessing that expression will not work with Python. But it looks like you are right in that we are trying to force a format on a field with a record that does not match that format. 

I will do some more research. I think I may have to use an insert cursor method so that it can check each record before calculating.

[AndrewL · Accepted solution]

I found an example on the Calculate Field examples

expression = "arcpy.time.ParseDateTimeString(!DATE_UTC!) + timedelta(days=-0.25)"‍

This worked for me. Thanks!