String to Date conversion help

[JamesCrandall]

I need to calculate a date field of a FGDB raster catalog (but I guess it could be any table or fc for that matter).  The field I need to convert is a string that contains date values in the format: '2013-09-23 10:01:50' or for your Python referenced format it is: '%Y-%m-%d %H:%M:%S'

My problem is that (I think) the ESRI Date field has trouble with really early dates.  For example, this how could I correctly set a date field value with this string:

'0001-01-01 00:00:00'

Yes, that is year "1".  When I run the code below, it calculates the field but this example value above is set to '1/1/2001', dropping the hh:mm:ss and always starts in the 21st century.  I need the date field to save it as the original string, but stored as a date --- I am using the Time Slider to create time-series animations and it is very finicky about using dates.

Any help is appreciated!

     fld = "_dateText_str"     updFld = "_dateText_str_Converted"     cursor = arcpy.UpdateCursor(ras)     for row in cursor:         strVal = row._dateText_str         dtVal = datetime.datetime.strptime(strVal, '%Y-%m-%d %H:%M:%S')         row.setValue(updFld, dtVal)         cursor.updateRow(row) 

[RichardFairhurst · Accepted solution]

I need to calculate a date field of a FGDB raster catalog (but I guess it could be any table or fc for that matter).  The field I need to convert is a string that contains date values in the format: '2013-09-23 10:01:50' or for your Python referenced format it is: '%Y-%m-%d %H:%M:%S'

My problem is that (I think) the ESRI Date field has trouble with really early dates.  For example, this how could I correctly set a date field value with this string:

'0001-01-01 00:00:00'

Yes, that is year "1".  When I run the code below, it calculates the field but this example value above is set to '1/1/2001', dropping the hh:mm:ss and always starts in the 21st century.  I need the date field to save it as the original string, but stored as a date --- I am using the Time Slider to create time-series animations and it is very finicky about using dates.

Any help is appreciated!

     fld = "_dateText_str"     updFld = "_dateText_str_Converted"     cursor = arcpy.UpdateCursor(ras)     for row in cursor:         strVal = row._dateText_str         dtVal = datetime.datetime.strptime(strVal, '%Y-%m-%d %H:%M:%S')         row.setValue(updFld, dtVal)         cursor.updateRow(row) 

The File Geodatabase date field only supports dates from 12/31/1899 forward.  I know this because the internal representation of the data is a number (exposed through the Summary Statistics tool).  I know the internal representation of July 28, 2013 is 41483 days, which only allows 113 years back to day 0.  The date field does not support negative numbers internally.  I verified that the number of days between 12/31/1899 and 7/28/2013 is 41483 in Excel.  So you will not be able to use an fgdb date field to do what you want.

This should be documented with the other field data type descriptions and limitations in the help, but it isn't.

[JamesCrandall]

The File Geodatabase date field only supports dates from 12/31/1899 forward.  I know this because the internal representation of the data is a number (exposed through the Summary Statistics tool).  I know the internal representation of July 28, 2013 is 41483 days, which only allows 113 years back to day 0.  The date field does not support negative numbers internally.  I verified that the number of days between 12/31/1899 and 7/28/2013 is 41483 in Excel.  So you will not be able to use an fgdb date field to do what you want.

This should be documented with the other field data type descriptions and limitations in the help, but it isn't.

Thanks Richard!

I'm open to suggestions on where to go from here.  I have 500 rasters in a raster catalog with a name sequence starting at "0001" and go thru to "0500".  I had hoped to use this as a Year value, which I could then use in time enabled properties and get it to animate based upon this.

[ZachLiu1]

If that's how you want to use the date field (not worrying about real date), add 2000 to each seems to be a easy solution. 🙂

[JamesCrandall]

If that's how you want to use the date field (not worrying about real date), add 2000 to each seems to be a easy solution. 🙂

Yep, that is the easy part!  🙂

But this is the problem:

The customer says, "Why does the label start at "2001"?  It should be "0001"!".  Since the dynamic time display label is based upon the date field, it cannot be manipulated or masked in a way that I could alter the 2001 to read 0001.  Maybe I am missing something simple there, but from what I have found this simply cannot be altered as needed.

Also, for some reason the "Export to Video" is sooo clunky.  I can never get it to export individual frames (1 year is 1 frame) and it wants to skip years in irregular intervals even though I have all of the appropriate properties set on the layer and time slider.  Very frustrating when you hit preview/play and it steps thru everything correctly but when attempting to export you get a video that starts at 2000 then year 2003, 20011, 2014... etc  --- it never exports 2001, 2002, 2003...etc...

[JamesCrandall]

Okay here's a solution I've arrived at:

1. I use the calculated date field (it can be whatever as long as it is in sequence)
2. Use another attribute field (in my case I have the name of the raster) from the raster catalog layer.  Turn on labels for that guy.
3. Remove the "Display date" from the animation (found on the time slider options menu).

I still struggle with how the sequence is set correctly in the final output/export.  It never seems consistent and I can never document how I am able to get it to export the frame-by-frame correctly (sometimes it wants to skip frames at irregular intervals for example).  I dunno, I just shut down, restarted the .mxd and reset things and amazingly it exports as intended --- incredibly frustrating to not be able to nail down why.

Anyway -- I over-thought a lot of things on this one.  But the time-slider just gets me sometimes and pushes me in a direction that appears to be what I need but clouds what it is that really needs to be done! 

Only thing left is to figure out how to position the label -- it sits directly in the middle of the raster.