topic Re: Selecting all records within a given date range mm/dd/yyyy in Spatial Data Science Questions

Selecting all records within a given date range mm/dd/yyyy

AlecKelly — Mon, 04 Aug 2014 21:35:39 GMT

Hello all,

I have a shapefile with a column of dates that were all manually entered in an editing session. Is it possible to create a "Select by Attributes" query that will select all records within a given date range such as from January 1st 2014 - May 31st 2014 (I want to be specific down to the day)? If it helps I am trying to get all of the records that occur within each business quarter.

I have tried a couple of ways to build a query that would do this task, but Arc seems to be real finnicky about how you query attributes that are in a date format.

Anyone know of a workaround to this problem, or know of a forum where this issue is discussed?

Thanks for the help.

Re: Selecting all records within a given date range mm/dd/yyyy

JoshWhite — Mon, 04 Aug 2014 21:40:33 GMT

Alec I tried

GPSDATE >= '2009-06-03 00:00:00' AND GPSDATE <= '2009-06-22 00:00:00'

For my feature this selected only features with a GPS Date of 6/3 to 6/22 and no others.

Re: Selecting all records within a given date range mm/dd/yyyy

AlecKelly — Tue, 05 Aug 2014 00:38:40 GMT

Do I need to establish a time somehow or is it included already within the Date data type? This looks great though, exactly what I was looking for! Thank you!

Re: Selecting all records within a given date range mm/dd/yyyy

JoshWhite — Tue, 05 Aug 2014 13:13:48 GMT

Mine didn't have times, I just selected those two dates from the list that is generated when you click Get Unique Values, it automatically put it in the form of 2009-06-03 00:00:00. So I guess the answer to your question is that yes, apparently the time is already included with the date type. The field is just displayed like this: 6/03/2009.

Re: Selecting all records within a given date range mm/dd/yyyy

AlecKelly — Tue, 05 Aug 2014 13:17:28 GMT

Yep, I just tried it like you said and it worked! I didn't realize Arc reformatted the date for you like that to make it simpler, I was trying to work in a bunch of conditional statements to sift through the dates.

Thanks for all of the help Josh!

Re: Selecting all records within a given date range mm/dd/yyyy

JoshWhite — Tue, 05 Aug 2014 13:26:22 GMT

Yeah I use that unique values button all the time for that reason, just to make sure that I am formatting the Query statement correctly.

Re: Selecting all records within a given date range mm/dd/yyyy

MathieuBoonen — Mon, 07 Oct 2019 01:56:52 GMT

I have a similar issue but what i wish to do is select records where date is greater than or equal to one specific date .

When I build a query the query the column tool converts the date to yyyy-mm-dd hh:mm:ss

e.g.Select OPDATE >= date '2007-03-25 00:00:00' the thing is it drops a bunch of records that it should pick up greater than this date.

I wonder if it has anything to do with American vs European date formats or how the record was keyed into the database. As all dates normally look like 25/03/2007 when viewed in the table i.e. dd/mm/yyyy as per:

So how do I ensure I get the correct records without missing any ,if you can imagine I have over 15000 records spanning 13 years I don't want to write a query for each and every potential date value).

eg OPDATE IN (date '2007-03-25 00:00:00' ,date '2007-03-26 00:00:00' ,date '2007-03-2700:00:00' ,date '2007-03-28 00:00:00' ,date '2007-03-29 00:00:00' .....)

( which equates to 4749 potential date results or 365.25 days a year for 13 years )

Re: Selecting all records within a given date range mm/dd/yyyy

JoshWhite — Mon, 07 Oct 2019 14:24:47 GMT

Is your field in DATE format or TEXT (STRING)? The example I was using involves dates in the DATE format. I've found the only way for a text field to properly work as a date for selection is to format it like this 20191007 (YYYYMMDD) if it represented today's date. It might work with YYYYDDMM or 20190710 to more closely match your format but I'm not sure). If it is a DATE field, you'll likely want to use unique values and ensure your date format matches how you enter it (hope that made sense).

Re: Selecting all records within a given date range mm/dd/yyyy

MathieuBoonen — Mon, 07 Oct 2019 19:33:27 GMT

The field is a date format , so unique values it is. Maybe I will create 3 fields day month and year as integer values, then calculate out each using text strings left and right functions etc and at least I can group them easily by month and year and query the day as a numeric value. I am surprised I have not seen this issue earlier as I have been using Arc products since 1991....Goes to show you are always learning when it comes to GIS....

Re: Selecting all records within a given date range mm/dd/yyyy

KrisKarki — Fri, 17 Mar 2023 12:59:52 GMT

Hey I have date field. how to select date from 1 month past from today. so tomorrow would be 1 month past from tomorrow in ArcGIS pro attribute table?

please and thank you,