topic Re: Cannot select by attributes with GDB_TO_DATE field in Python Questions

Cannot select by attributes with GDB_TO_DATE field

MKF62 — Sat, 11 Dec 2021 14:37:09 GMT

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)

Re: Cannot select by attributes with GDB_TO_DATE field

JohannesBierer — Mon, 18 Nov 2019 14:48:45 GMT

What happens if you use: .

toDate = fromDate.strftime('%Y-%m-%d %I:%M:%S %p') ?

Re: Cannot select by attributes with GDB_TO_DATE field

MKF62 — Mon, 18 Nov 2019 14:58:54 GMT

Thanks for the suggestion but unfortunately that doesn't work either (and I put in the missing colon).

Re: Cannot select by attributes with GDB_TO_DATE field

JohannesBierer — Mon, 18 Nov 2019 15:06:13 GMT

Maybe 11/18/2019 doesn't fit to your code?

Re: Cannot select by attributes with GDB_TO_DATE field

MKF62 — Sat, 11 Dec 2021 14:37:12 GMT

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‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Cannot select by attributes with GDB_TO_DATE field

MKF62 — Mon, 18 Nov 2019 15:42:03 GMT

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.

Re: Cannot select by attributes with GDB_TO_DATE field

JohannesBierer — Mon, 18 Nov 2019 15:44:56 GMT

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'