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

2219
3
Jump to solution
12-17-2019 08:14 AM
AndrewL
Occasional Contributor II

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")
0 Kudos
1 Solution

Accepted Solutions
AndrewL
Occasional Contributor II

I found an example on the Calculate Field examples

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

This worked for me. Thanks!

View solution in original post

3 Replies
MichaelBoyce
Esri Contributor

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
Occasional Contributor II

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.

0 Kudos
AndrewL
Occasional Contributor II

I found an example on the Calculate Field examples

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

This worked for me. Thanks!