Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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
share|improve this question
    
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
add comment

1 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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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