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
  • Are you sure that your restoretime field actually contains time information and not just the date? Will your query work if you replace CURRENT_TIME with CURRENT_DATE? Are your sure that your FGDB is of v10 flavor as it seems that the CURRENT_TIME function is new as of v10 as it is not mentioned in the 9.3 documentation - webhelp.esri.com/arcgisdesktop/9.3/… – Brent Edwards May 10 '12 at 19:56

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.

  • thanks for the response. however, it looks the CURRENT_TIMESTAMP not available until 10.1 - forums.arcgis.com/threads/… – user890 May 11 '12 at 12:17
  • so is there any solutions to comparing date in 10.0? – jonleech Jan 29 '13 at 2:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.