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

【解决方案】
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.


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


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



【其它相关参考】

1. Microsoft: DATEPART sql
2. Version 10 help: Building a query expression
3. Version 10 help: SQL reference for query expressions used in ArcGIS

【创建及修改时间】
Created: 3/29/2012

Last Modified: 4/24/2012
【原文链接】
http://support.esri.com/en/kno ... 40088