Is This Content Helpful?
We're glad to know this article was helpful.
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.
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.
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
EXTRACT(MONTH FROM "MyDate") = 03
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.
These use Microsoft's DatePart function to extract a portion of the date.
DATEPART(PART, DATEFIELD) = VALUE
DATEPART("m", [MyDate]) = 03
DATEPART("h", [MyDate]) < 12
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