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

[AlecKelly]

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.

[JoshWhite · Accepted solution]

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.

[AlecKelly]

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!

[JoshWhite]

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.

[AlecKelly]

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!

[JoshWhite]

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. 

[MathieuBoonen]

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 )

[JoshWhite]

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).  

[MathieuBoonen]

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....

[KrisKarki]

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,