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
Best Answer
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:
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.