topic Select Records 90 days from Current Date using DAY and MONTH in Geodatabase Questions

Select Records 90 days from Current Date using DAY and MONTH

timdunlevie1 — Fri, 19 Oct 2018 06:40:49 GMT

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

Re: Select Records 90 days from Current Date using DAY and MONTH

KevinDunlop — Fri, 19 Oct 2018 13:01:09 GMT

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'‍

Re: Select Records 90 days from Current Date using DAY and MONTH

timdunlevie1 — Fri, 19 Oct 2018 13:04:52 GMT

Thanks.

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

earlier so that query will not work?

Re: Select Records 90 days from Current Date using DAY and MONTH

KevinDunlop — Fri, 19 Oct 2018 13:10:29 GMT

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.

Re: Select Records 90 days from Current Date using DAY and MONTH

timdunlevie1 — Mon, 22 Oct 2018 03:28:47 GMT

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

Re: Select Records 90 days from Current Date using DAY and MONTH

JoshuaBixby — Mon, 22 Oct 2018 15:25:42 GMT

What about "CURRENT_DATE - START_DATE < 90"

Re: Select Records 90 days from Current Date using DAY and MONTH

KevinDunlop — Sun, 12 Dec 2021 09:29:33 GMT

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.

Re: Select Records 90 days from Current Date using DAY and MONTH

timdunlevie1 — Mon, 29 Oct 2018 05:00:57 GMT

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.

Re: Select Records 90 days from Current Date using DAY and MONTH

JoshuaBixby — Sun, 12 Dec 2021 09:29:36 GMT

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),)
>>> ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Select Records 90 days from Current Date using DAY and MONTH

timdunlevie1 — Tue, 30 Oct 2018 00:44:16 GMT

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.

Re: Select Records 90 days from Current Date using DAY and MONTH

DanPatterson_Retired — Tue, 30 Oct 2018 05:43:13 GMT

instead of using 'now', you would substitute a different value in for it... the logic would still apply