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?

  • 1
    Maybe it's easier to split the field in two with space as separator? Then you can get a field with time only, which probably is more convenient to filter from. – Martin Jun 3 '13 at 13:00
  • Shouldn't this be more generally stated as "how do I select by time of day on a date field?" ? – Kirk Kuykendall Jun 3 '13 at 14:37
  • I guess you have referred to the esri help. But here is the link resources.arcgis.com/en/help/main/10.1/index.html#//… – Brad Nesom Jun 3 '13 at 15:19

If you date is stored as a date you can use this:

EXTRACT(HOUR FROM "Date_filed" ) = 6 
EXTRACT(HOUR FROM "Date_filed" ) = 7 
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:_____'
"Date_filed" LIKE '___________07:_____'
"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:%'
"Date_filed" LIKE '% 07:%'
"Date_filed" LIKE '% 08:%'

but as Martin suggested splitting the datetime up and adding a time field might be the best way to go.

  • Dont you need to put date after the LIKE statement? Ill do some testing now... – Simon Jun 3 '13 at 13:51
  • In the first query the underscores represent the characters of the dates. In the second the using the wildcard then space then your hour marker followed by : should allow for selecting only the hours, since there is no other position in your datetime that will meet that criteria. – Hotpepper Jun 3 '13 at 13:53
  • This (and variations) is not working for me, Keep getting 'an invalid SQL statement was used' – Simon Jun 3 '13 at 13:54
  • You are changing "date_field" to the name of your datetime field right? Also you may need to change "date_field" to [date_field]. – Hotpepper Jun 3 '13 at 13:55
  • Sorry, I edited my answer, to deal with date format. – Hotpepper Jun 3 '13 at 14:19

I believe you can use the subs in the date field without creating a new field for hour.
See this question using the day sub.
Just switch to the hour sub and use the between.

Your Answer

