5

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.

Evil Genius
  • 6,289
  • 2
  • 27
  • 40
Kenton Williams
  • 96
  • 1
  • 3

2 Answers2

1

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
Kirk Kuykendall
  • 25,787
  • 8
  • 65
  • 153
0

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())

Mapperz
  • 49,701
  • 9
  • 73
  • 132
  • 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