ESRI File Geodatabase - Trying to use CURRENT TIME in Definition Query

Question

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

Answer 1

Since the field you are basing your definition query on contains both date and time, you cannot just use the "CURRENT_TIME" function as the comparison. You have to use a function that compares both date and time. The ArcGIS desktop help states that queries use standard SQL syntax: SQL reference for query expressions used in ArcGIS

If you look at the SQL.org website for Date/Time Functions and Operators, you see a reference for CURRENT_TIMESTAMP.

Here are what the different functions return:
CURRENT_TIMESTAMP: Complete date and time expression: 2001-12-23 14:39:53
Current_TIME: Current Time: 14:39:53
Current_DATE: Current Date: 2001-12-23
Section 6.8.4 of that page specifically discusses the Current Date/Time functions.

So your expression should probably look something like this:

"OBJECTID" IN (SELECT "assetid" from dummydata WHERE "restoretime" < CURRENT_TIMESTAMP)

The one thing you want to ensure is that the field you are querying against is defined as a date/time field, or else the comparison won't work. From the look of the sample data you included, it probably is, so you shouldn't have a problem.

Hope this helps.