Is it me, or are the date functions in the file geodatabase a total nightmare.
Anyway, I am trying to set up a fairly simple definition query that contains a subquery with a date comparison. My definition query looks like this:
"OBJECTID" IN (SELECT "assetid" from dummydata WHERE "restoretime" < CURRENT_TIME)
According the ESRI SQL Reference, the current time function is defined as CURRENT TIME (no underscore) but that breaks the definition query altogether. With the underscore, I dont get any errors, but the selection set is always null. Every row in dummydata has a restoretime value set which is definitely older than the current time. What am I doing wrong here?
FYI - ArcMap 10 SP4
UPDATE: Some sample rows from the dummydata table
OID assetid outagetime restoretime
1 1 5/10/2012 3:06:59 PM 5/10/2012 3:07:04 PM
2 2 5/10/2012 3:06:59 PM 5/10/2012 3:09:14 PM