14

I would like to select by a specific month from my date/time field in ArcGIS, and just can't seem to find a statement that will work.

I have multiple years and multiple days, but want to filter those out and only choose the records from the month of May.

Improve this question
6
  • Where is your data stored? fGDB? shapefile? SQL Server? Oracle?
    – Derek Swingley
    Apr 5, 2011 at 20:54
  • How are you searching for the data? I know there is a bug where the time element of datetime doesn't show if the time is midnight. Remember, too, that locale is important in the order of data, months, especially so. There are a whole host of operations you can use in Python or VBS to disseminate date data, as well as on the Oracle side (Between, etc...).
    – Hairy
    Apr 6, 2011 at 10:18
  • Data is stored in a shapefile.
    – user2595
    Apr 6, 2011 at 15:21
  • thank you! was useful - I did my selection by dates very quickly!
    – user5133
    Dec 13, 2011 at 0:20
  • welcome to GIS.StackExchange. The "Your Answer" is meant for potential answers to the question at hand, not comments. Feel free to leave a comment under the answer that worked best for you, and press the "upvote" button to give more credibility to that answer. That way new readers will be able to guage which answer is most likely to help.
    – RyanKDalton
    Dec 13, 2011 at 0:37

7 Answers 7

Reset to default
8

The syntax varies depending on where the data is being fetched from (file gdb, shapefile, personal gdb, Oracle, DB2, etc.).

File Geodatabase, shapefiles, and file-based data such as dbf files:

Everything in May:

EXTRACT(MONTH FROM "MyDate") = 05

Before noon:

EXTRACT(HOUR FROM "MyDate") < 12

Personal Geodatabase (.mdb): 

DATEPART("m", [MyDate]) = 05

SQL Server:

DATEPART(month, MyDate) = 05

For more details such as further limitations and query by hour/year/etc. see the Esri HowTo: Search for specific parts of a date stored in a Date-Time field. For extended examples using Field Calculator see ArcWatch Simplify Date and Time Calculations.

Improve this answer
1
  • 2
    Just want add a comment to help others reading this answer. I was making the mistake of wrapping the month value in quotes (e.g. EXTRACT(MONTH FROM "MyDate") = '05') thinking I was returning a string but EXTRACT returns the day, month or year as an integer.
    – Hornbydd
    Feb 11, 2016 at 16:05
8

Perhaps something like this:

  1. Open the shapefile attribute table and click Options > Select By Attributes
  2. Type DatePart("M", [YourDateField]) = 5 (for May) and click Apply.
Improve this answer
1
  • This code sample works really well. I actually used it to calc a new field values for month based upon the Month DatePart.
    – RyanKDalton
    Apr 6, 2011 at 21:26
6

In ArcGIS 10 (probably works similarly in earlier versions in the select by attributes tool you can specify the selection statement to have a range between the beginning of the month and the end of the month, but the syntax in ArcMap is strange.

The selection query should look like this:

"DateField" >= date '05/01/2011 00:00:00' AND "DateField" < date '06/01/2011 00:00:00'

I used excel to make a long list of month ranges and then used model builder to make a script to batch select by the above query, update an ‘order by’ field, and then export to a standalone filegeodatabase featureclass.

Improve this answer
2
  • my source data is in a file geodatabase.
    – Max Squires
    Apr 10, 2011 at 17:11
  • 1
    This works! It is too bad that you would have to write it out for EACH year in the dataset. Thanks!
    – user2595
    Apr 11, 2011 at 15:14
3

Try something like... 

Select DatePart(MM, GetDate()) as Current_Month
Improve this answer
2

You should be able to use the appropriate database function to query out the month from the date field. For example, in Oracle you could select by attribute where To_Char([date],'MM') = 4.

Improve this answer
1

A simple script to write would be to get the date in it's entirety, then split it:

Time = "2011-04-06 11:26:51"
Month = newTime.split("-")[1]

Then do your comparison on the Month value

Improve this answer
0

Horrible hack, but you could export your attribute table to .csv; import into Excel, and parse the year/month/day in excel. Then join that Excel worksheet to your table, and sort on the month.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.