[GIS] Selecting by month in date/time field using ArcGIS Desktop

arcgis-desktopdateselect-by-attribute

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.

Best Answer

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.

Related Solutions

ArcGIS for Desktop – Finding Differences Between Shapefiles

With the Compare Feature Tool, you should just choose your Segment_ID field as the sort field in the dialog.

The [sort] field or fields [are] used to sort records in the Input Base Table and the Input Test Table. The records are sorted in ascending order. Sorting by a common field in both the Input Base Features and the Input Test Features ensures that you are comparing the same row from each input dataset.

If you are wanting to get the Segment_ID field as part of your output table, try joining the output Compare Feature table to your source Test Table on cf.Object_ID=tt.Object_ID, then export out the joined table with your Segment_ID.

You could easily put this into a model for easier repeatability.

[GIS] Select by Attribute on Date field using wildcards

If you date is stored as a date you can use this:

EXTRACT(HOUR FROM "Date_filed" ) = 6 
or
EXTRACT(HOUR FROM "Date_filed" ) = 7 
or
EXTRACT(HOUR FROM "Date_filed" ) = 8

IF your date is stored as a text field you could use this:

It's a little ugly, but you could use this

"Date_filed" LIKE '___________06:_____'
or
"Date_filed" LIKE '___________07:_____'
or
"Date_filed" LIKE '___________08:_____'

and just change the numbers to indicate the hours you are interested in.

or this should work:

"Date_filed" LIKE '% 06:%'
or
"Date_filed" LIKE '% 07:%'
or
"Date_filed" LIKE '% 08:%'

but as Martin suggested splitting the datetime up and adding a time field might be the best way to go.

Best Answer

Related Solutions

ArcGIS for Desktop – Finding Differences Between Shapefiles

[GIS] Select by Attribute on Date field using wildcards

Related Question