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" ) table_preview.jpg David_ Sardin_1_resize_recadre.jpg

I found an example on the Calculate Field examples expression = "arcpy.time.ParseDateTimeString(!DATE_UTC!) + timedelta(days=-0.25)" This worked for me. Thanks!

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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'

<SPAN class="comment token"># Add CST date/time field</SPAN>
<SPAN class="keyword token">print</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="string token">"Adding CST date field"</SPAN><SPAN class="punctuation token">)</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>AddField_management<SPAN class="punctuation token">(</SPAN>mergedFCSquaresDis<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"DATE_CST"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"Date"</SPAN><SPAN class="punctuation token">)</SPAN>
expression <SPAN class="operator token">=</SPAN> <SPAN class="string token">"datetime.strptime(!DATE_UTC!,'%m/%d/%Y %I:%M:%S %p') + timedelta(hours=-6)"</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>CalculateField_management<SPAN class="punctuation token">(</SPAN>mergedFCSquaresDis<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"DATE_CST"</SPAN><SPAN class="punctuation token">,</SPAN> expression<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"PYTHON_9.3"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

table_preview.jpg

David_ Sardin_1_resize_recadre.jpg

Find more posts tagged with

Accepted answers

AndrewL

I found an example on the Calculate Field examples

expression <SPAN class="operator token">=</SPAN> <SPAN class="string token">"arcpy.time.ParseDateTimeString(!DATE_UTC!) + timedelta(days=-0.25)"</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

This worked for me. Thanks!

All comments

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.

<SPAN class="operator token">-</SPAN><SPAN class="operator token">-</SPAN><SPAN class="operator token">-</SPAN><SPAN class="operator token">-</SPAN><SPAN class="comment token"># Add CST date/time field</SPAN>
<SPAN class="keyword token">print</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="string token">"Adding CST date field"</SPAN><SPAN class="punctuation token">)</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>AddField_management<SPAN class="punctuation token">(</SPAN>mergedFCSquaresDis<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"DATE_CST"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"Date"</SPAN><SPAN class="punctuation token">)</SPAN>
expression <SPAN class="operator token">=</SPAN> <SPAN class="string token">"!DATE_UTC! - 0.25"</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>CalculateField_management<SPAN class="punctuation token">(</SPAN>mergedFCSquaresDis<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"DATE_CST"</SPAN><SPAN class="punctuation token">,</SPAN> expression<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"PYTHON_9.3"</SPAN><SPAN class="punctuation token">)</SPAN>‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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

I found an example on the Calculate Field examples

expression <SPAN class="operator token">=</SPAN> <SPAN class="string token">"arcpy.time.ParseDateTimeString(!DATE_UTC!) + timedelta(days=-0.25)"</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

This worked for me. Thanks!

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.