topic Re: Query by date range in ArcObjects SDK Questions

Query by date range

LeonelTorres — Thu, 23 Jun 2011 16:39:58 GMT

I have a feature class containing a text field which has the date a feaure was added and the user. The format is "yyyymmdd-UserName". What I would like to be able to do is select features based on a date range using VBA. Any help will be much appreciated it.

Thank you,
Leonel

Re: Query by date range

AlexanderGray — Thu, 23 Jun 2011 17:00:06 GMT

I would recommend creating a separate date field and use the field calculator to copy only the part before the dash into it as a date. Then you can query dates with the greater than, less than comparison operators and date function. You then don't have to write any code to do it.
If you must keep the field as is, depending on the database the data is in, in the where clause of a query filter, you can use string functions to recover the date part of the string, convert it to date and use a date comparison syntax supported by your database.

Re: Query by date range

LeonelTorres — Thu, 23 Jun 2011 17:12:04 GMT

I would prefer to use code and not create another field. I'm trying to create a form where the user inputs the date range and the features are then selected. I'm not sure how to grab the date in the string and convert it to a date format to query on it.

Re: Query by date range

AlexanderGray — Thu, 23 Jun 2011 19:45:15 GMT

In that case the syntax will be tricky. You will need to see which string functions apply to your database's SQL.
for featureclasses in oracle you can use INSTR to find the position of the dash, SUBSTR to get only the part before the dash, TO_DATE to convert it to date and finally you can do '<', '>' operations with the dates in the where clause.
it works out to something similar to this in Oracle
where = "TO_DATE( SUBSTR(DateUserField,1, INSTR(DateUserField, '-') -1), 'YYYYMMDD') < TO_DATE('23-06-2011', 'DD-MM-YYYY') AND TO_DATE( SUBSTR(DateUserField, INSTR(DateUserField, 1, '-') -1), 'YYYYMMDD') > TO_DATE('22-06-2011', 'DD-MM-YYYY')"

For file geodatabase or shapefile, I don't know if you can do the string functions.

Re: Query by date range

LeonelTorres — Mon, 27 Jun 2011 13:59:31 GMT

Thanks for your help. I will have to find out how to do it in SQL Server.

Re: Query by date range

IngridHogle — Mon, 09 Mar 2020 17:10:52 GMT

I have this exact same situation and need, using SQL Server Express. Were you able to find a way to do what you described here?