1

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?

|improve this question
  • 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
1

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.

|improve this answer
  • 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
0

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.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.