How To: Search for specific parts of a date stored in a Date-Time field

Summary

Instructions provided describe some examples for writing SQL statements to select only portions of a date-time field, which can then be used in WHERE clauses throughout ArcGIS.

Procedure

Sometimes comparisons need to be done on dates that are not just a date range. Parts of the date field can be extracted to take advantage of the different values found in the date-time field. This functionality is similar to the LIKE function for strings.

For example, it may be desirable to search through data and select every event that happens in March.

The following examples show how to do this for file geodatabases, shapefiles, dbf files, and personal geodatabases.

Note:
Please note that the method for parsing these parts of the data varies based on database used to store the data.

A few things to remember before proceeding:

• The database type controls the syntax that is used for field names. For example, gdb/shapefile/dbf: "Field"; mdb: [field]; and ArcSDE: FIELD.
• These functions only work on true date-time fields and not text fields.
• Use the Verify button when available in ArcGIS to check that the statement is correctly written.

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

The standard view would look like this:

EXTRACT(extract_part FROM extract_source) = VALUE

This returns the 'extract_part' portion of the 'extract_source'. The extract_source argument is a date-time field. The extract_part argument can be one of the following keywords: YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND. Other operators — such as <, >, <>, <=, >=, etc. — can be used as well.

1. This example pulls the month of March from a file geodatabase with a date field called MyDate:

EXTRACT(MONTH FROM "MyDate") = 03

2. This example finds all the hours before noon:

EXTRACT(HOUR FROM "MyDate") < 12

Note:
Time parts cannot be called from a shapefile or file-based data because they only support the use of DATE and not TIME.

Personal Geodatabase

These use Microsoft's DatePart function to extract a portion of the date.

DATEPART(PART, DATEFIELD) = VALUE

The PART argument can be one of the following keywords: yyyy (YEAR), m (MONTH), d (DAY), h (HOUR), n (MINUTE), or s (SECOND). Other operators — such as <, >, <>, <=, >=, etc. — can be used as well.

1. This example pulls the month of March from a personal geodatabase with a date field called MyDate:

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

2. This example finds all the hours before noon:

DATEPART("h", [MyDate]) < 12

Other databases

Please consult the database's function documentation and syntax.

For example, SQL server uses the same DATEPART function as personal geodatabases, but with slightly different syntax:

DATEPART(year, MyDate) = 2012

Related Information

Last Published: 12/28/2016

Article ID: 000011531