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.

I am trying to build a where clause for a select by attributes operation in ArcGIS. I have a feature class that has a date field that has dates in it. I wish to construct a where clause that selects records from 65 years ago or more. I want this to be generic, where the clause computes the 65 years from whatever today's date is without actually entering today's date. In other words, I do NOT want to enter something for a where clause like this:

"date_field" >= date '1945-09-22'

I want to have a where clause like this:

"date_field" >= curdate() - 65 years

The problem is that I can't figure out how to write this where clause. There is nothing in the ArcGIS documentation on composing where clauses with dates that includes obtaining the current date programmatically as opposed to entering a specific date.

Help? Any input is appreciated.

share|improve this question
    
What's the underlying database? –  Derek Swingley Sep 22 '10 at 16:38
    
It's a file geodatabase. –  Kenton Williams Sep 22 '10 at 18:29

2 Answers 2

ESRI documentation says it follows SQL 92.

SQL 92 says subtraction of dates returns number of days.

This works with a file geodatabase to select rows older than 65 years:

(current_date - "DateField") > 365.0 * 65.0
share|improve this answer
    
This works perfectly! Thanks for your assistance! –  Kenton Williams Sep 22 '10 at 18:18
    
Glad to hear it, please check my response as the accepted answer. –  Kirk Kuykendall Sep 22 '10 at 18:43

If not ArcGIS10...

Calculating an attribute to the number of days between two dates

  1. Click Editor on the Editor toolbar and click Start Editing. You can make calculations without being in an editing session; however, in that case, there is no way to undo the results.
  2. Click the Source tab on the table of contents.
  3. Right-click the layer or table you want to edit and click Open Attribute Table.
  4. Right-click the heading for the field to show the number of days between the date and the present date and click Calculate Values. If there is no field for the new values, add a new field by clicking the Options button and then clicking the Add Field option. To add a new field, you need to exit the editing session.
  5. In the field calculator text box, enter the following calculation expression:

    DateDiff("d", [date_field], Now())

share|improve this answer
    
If I needed a field calculation this would be helpful, but I need a Select By Attributes where clause. The answer from Kirk Kuykendall below did the trick. Thanks! –  Kenton Williams Sep 22 '10 at 18:19

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.