Is This Content Helpful?
How can we make this better? Please provide as much detail as possible.
Contact our Support Team
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.
• 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:
2. This example finds all the hours before noon:
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:
2. This example finds all the hours before noon:
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
- Microsoft: DATEPART sql
- Version 10 help: Building a query expression
- Version 10 help: SQL reference for query expressions used in ArcGIS
Last Published: 12/28/2016
Article ID: 000011531
Software: ArcGIS-ArcEditor 9.3.1, 9.3, 10 ArcGIS-ArcInfo 9.3.1, 9.3, 10 ArcGIS-ArcView 9.3.1, 9.3, 10