Hey folks, so I came across this last night, my quick searching yielded no obvious results. Please read and let me know if anyone else has seen this.
Problem: Given a table or feature class (probably anything built on a table), with a DATE field, when the date is calculated or updated via arcpy with the function datetime.datetime.now(), the value is visible in the attribute table to the second resolution and visible within auto-populated values when creating a def query or select by attributes query. If the def query is equals or included in the def query and select query yield no results. When the same field is populated manually in the attribute table the results are expected. When the calculation is performed with datetime.datetime.now().replace(microsecond=0), the results are as expected.
My guess: The code that displays the date/time rounds to the nearest second for end-user visualization, but the underlying values in the GDB are at the microsecond and the the sql engine is taking in the values rounded to the nearest second since that's what the end user selected via the UI.
To Replicate: Create a new file geodatabase. Create a Table with two fields a text field and a date field. Add three values to the table:
Case 1/ Enter date/time manually via attribute table.
Case 2/ Enter date/time via calculate field using datetime.datetime.now()
Case 3/ Enter date/time via calculate field using datetime.datetime.now().replace(microsecond=0)
Try to select each case using select by attributes with either = or includes values.
Try using def query with same sql statements.
Example with equals and IN (which displays as 'includes the value(s)'):
mydatefield = timestamp '2023-06-08 23:00:37'
mydatefield IN (timestamp '2023-06-08 23:00:37', timestamp '2021-08-30 00:00:00', timestamp '2021-08-31 00:00:00')
Workaround: If calculating date/time values round to the nearest second.
I wanted to put this in this forum before trying to submit a bug.
Observed in ArcGIS Pro 2.9.5 and 3.1.2
Thanks
George
