Select Records 90 days from Current Date using DAY and MONTH

[timdunlevie1]

Hi all,

I have a feature class in a fgdb (ArcMap 10.6) and I want to add a definition query which shows me records that have a 'Start_Date' within 90 days of the current date.

The issue I have is that the Date field (DD/MM/YYYY) I am using, I can only use the DD/MM portion of this field as the year part is not a future year date.

For example:

ID, Start_Date

1, 02/11/2015

2, 10/12/2001

3, 10/01/2011

Is there a way to use the day & month of the "Start_Date" field to determine  if the Start_Date is within 90 days of today's date or Current Date.

My filter so far is this:

(EXTRACT(MONTH FROM Start_Date) >= EXTRACT(MONTH FROM CURRENT_DATE)) and (EXTRACT(MONTH FROM Start_Date) <= EXTRACT(MONTH FROM CURRENT_DATE)+3)

This is using the MONTH field, and it works to a degree, but is not quite precise.

It also has a problem with dates that fall in January as those records are not selected by add +3 from current month (10).

Can you combine EXTRACT(DAY....) AND EXTRACT(MONTH...) in the same query? I've been trying but keep getting a syntax error.

thanks

[KevinDunlop]

This depends on your database. 

For SQL Server the easiest way is 

Start_Date >= getdate() - 90‍

For PostgreSQL it is:

Start_Date >= now() - INTERVAL '90 DAY'‍

[timdunlevie1]

Thanks.

But the Start Date is not the current year...it is normally 2017 and

earlier so that query will not work?

[KevinDunlop]

I missed the part where you said it was FGDB.

That would be:

Start_Date >= CURRENT_DATE -90

If you don't use something like this and only the DD/MM then the years will be an issue.  The method you are trying would return records 2 and 3 when they are both pass 90 days ago.

[timdunlevie1]

Thanks Kevin.

However my problem is to somehow use just the day and month of the Start_Date as the years are all different and are not the current year.

20/05/2016

01/01/2017

etc..

thanks,

Tim

[JoshuaBixby]

What about "CURRENT_DATE - START_DATE < 90"

[KevinDunlop]

I am not sure I understand your problem.  Are you interested in finding all calendar dates that fall within 90 days of this time each year. Or are you trying to find all dates that are 90 days within a provided past date?  Those two problems would have different solutions.

For the first question.  It is not perfect since it assumes each month has exactly 30 days.  

(EXTRACT(MONTH FROM Start_Date) - 1) * 30 +  EXTRACT(DAY FROM Start_Date) 
BETWEEN ((EXTRACT(MONTH FROM CURRENT_DATE) - 1) * 30 +  EXTRACT(DAY FROM CURRENT_DATE) - 90) 
AND ((EXTRACT(MONTH FROM CURRENT_DATE) - 1) * 30 +  EXTRACT(DAY FROM CURRENT_DATE))‍‍‍‍‍‍‍‍‍

That means for today this would return values like

8/12/2016

9/15/2018

10/22/2003

You can easily substitute CURRENT DATE with any date (see below as example).

As for the second one here it could be any date:

Start_date >= date '2017-10-22' - 90 and start_date <= date '2017-10-22'‍

This will return all dates between July 24, 2017 and Oct 22, 2017.  

In both solutions, it assumes you are interested in only 90 days before the given date.  You can change this by adding 90 to second date listed.

[timdunlevie1]

awesome - thanks Kevin.

Believe this works:

(EXTRACT(MONTH FROM Start_Date) - 1) * 30 + EXTRACT(DAY FROM Start_Date)

BETWEEN ((EXTRACT(MONTH FROM CURRENT_DATE) - 1) * 30 +  EXTRACT(DAY FROM CURRENT_DATE)) AND ((EXTRACT(MONTH FROM CURRENT_DATE) - 1) * 30 +  EXTRACT(DAY FROM CURRENT_DATE)+90)

whilst not perfect ...as you say as we are assuming each month is 30 days, it's probably close enough to what I need.

So this shows me records within 90 days of the current date.

[JoshuaBixby]

If you want to select all records whose START_DATE is within 90 days of the current date, all you need is my suggestion above:  "CURRENT_DATE - START_DATE < 90"

>>> # python3
>>> 
>>> import arcpy
>>> from datetime import datetime as dt, timedelta as td
>>>
>>> sample_dates = [
...     "02/11/2015",
...     "10/12/2001",
...     "10/01/2011",
...     "{:%m/%d/%Y}".format(dt.now()- td(days=89)),
...     "{:%m/%d/%Y}".format(dt.now()- td(days=91))
... ]
>>>
>>> sgdb = arcpy.env.scratchGDB
>>> tbl = arcpy.CreateTable_management(sgdb, "tbl")
>>> arcpy.AddField_management(tbl, "START_DATE", "DATE")
<Result 'C:\\Users\\user\\AppData\\Local\\Temp\\1\\scratch.gdb\\tbl'>
>>> with arcpy.da.InsertCursor(tbl, "START_DATE") as cur:
...     for date in sample_dates:
...         cur.insertRow([date])
...
1
2
3
4
5
>>> # print all records
>>> print(*arcpy.da.SearchCursor(tbl, "START_DATE"), sep="\n")
(datetime.datetime(2015, 2, 11, 0, 0),)
(datetime.datetime(2001, 10, 12, 0, 0),)
(datetime.datetime(2011, 10, 1, 0, 0),)
(datetime.datetime(2018, 8, 1, 0, 0),)
(datetime.datetime(2018, 7, 30, 0, 0),)
>>> 
>>> # print records where START_DATE within 90 days of now
>>> sql = "CURRENT_DATE - START_DATE < 90"
>>> print(*arcpy.da.SearchCursor(tbl, "START_DATE", sql), sep="\n")
(datetime.datetime(2018, 8, 1, 0, 0),)
>>> ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[timdunlevie1]

thanks Josh.

Yeah but my problem is that I can't use the YEAR of the START_DATE....as

they are historic years.

The "anniversary" occurs each year after the START_DATE and therefore I

need to grab the DAY & MONTH to work this out.