[GIS] Select by Attribute on Date field using wildcards

arcgis-10.1arcgis-desktopselect-by-attribute

I have a date field within a file geodatabase (10.1).

An example of a value in this field: 19/11/2012 08:49:56

I want to select all rows where the time is between 9pm to 11pm AND 6am to 9am.

I don't care what day, month, year it is. So I want to make use of wildcards to ignore these values.

What would be the correct statement to put in the Select By Attributes to achieve this?

Best Answer

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.

Related Question