Cannot select by attributes with GDB_TO_DATE field

Question

Has anybody encountered this problem? I have tried using both a datetime object and a string formatted object in the query and neither work in either python or ArcMap. I am trying to select a record by both its GlobalID and a specific time in the GDB_TO_DATE field on a query layer. Query: coveyObsvRow[0] = '{1309DFFD-04B3-4F13-9962-C1C378F1F228}' toDate = fromDate.strftime('%Y-%m-%d %H:%M:%S') query = "GlobalID = '{0}' and GDB_TO_DATE = '{1}'".format(coveyObsvRow[0], toDate) #query prints as GlobalID = '{1309DFFD-04B3-4F13-9962-C1C378F1F228}' and GDB_TO_DATE = '2019-11-18 13:58:04'‍‍‍‍‍‍‍‍‍‍ If I select by just using the GlobalID, I can return the records, so it's obviously a problem with the TO_DATE field, but I can't figure it out. I can see the record blatanly in the archive table: (1:58:04 is really stored as 13:58:04 in the database and you will see as much in the ArcMap select by attributes dialog)

JB1234510 · Answer

Don't know where your data comes from? Just saw your example.

If I have a date field in a file gdb I have to quercy it in the select by attributes dialog in ArcMaP like this?

date = date '2019-11-18 16:32:01'

MKF62 · Answer

I found another workaround. In my case, I needed a field that was unique to the record I was trying to select and I can obtain that field using a search cursor (I'm already using an update cursor in my script though so I just grabbed it from that). GDB_ARCHIVE_OID is the only unique field for an archived record that is not a date. Since dates don't work, I had to go with this. This probably is not a solution for everyone though, so I am leaving this question unanswered.

MKF62 · Answer

I'm not sure what you mean. You could reformat the datetime into '%m/%d/%Y %I:%M:%S %p' if you wanted to. When you use a datetime field, it automatically formats it to 11/18/2010 H:M:S AM/PM. When it's in the actual database it is formatted as 2019-11-18 H:M:S - this is the one that should logically work. The only workaround I can find is by making another query layer and using a >= operator instead of an = operator. Not very helpful because I need to maintain my current query layer for iteration and if I create a new one on the same set of data, it erases my last query layer. You also cannot select a single record this way, even when using a combination of GDB_FROM_DATE >= and GDB_TO_DATE <= language. queryLyr = arcpy.MakeQueryLayer_management(collector_db_con, 'output', "select * from COVEYOBSERVATIONTEST where GlobalID = '{0}' and GDB_TO_DATE >= '{1}'".format(coveyObsvRow[0], toDate)) #toDate is a datetime object, not a string‍‍‍‍‍‍‍‍‍‍‍‍‍