topic Re: Query feature if an attribute is null after 180 days of date on another attribute? in Python Questions

Query feature if an attribute is null after 180 days of date on another attribute?

Anonymous User — Fri, 05 Oct 2018 16:50:44 GMT

Looking for a sample ArcPy script I can modify for my use. Any suggestions?

Re: Query feature if an attribute is null after 180 days of date on another attribute?

RandyBurton — Sun, 12 Dec 2021 07:44:57 GMT

Perhaps (you could remove time from formatting, if desired):

from datetime import datetime, timedelta

date_past = datetime.now() - timedelta(days=180) # you can add/subtract as required

wc = "Field IS NULL AND DateField < DATE '{}'".format(date_past.strftime("%Y-%m-%d %H:%M:%S"))

print wc
# Field IS NULL AND DateField < DATE '2018-04-08 09:25:35'‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Query feature if an attribute is null after 180 days of date on another attribute?

Anonymous User — Fri, 05 Oct 2018 18:12:07 GMT

The month and day are correct but the year is 2019 instead of 2018?

Re: Query feature if an attribute is null after 180 days of date on another attribute?

RandyBurton — Fri, 05 Oct 2018 18:15:23 GMT

Typo on my part, an extra minus in days=180 (updated original code):

date_past = datetime.now() - timedelta(days=180)‍‍

Re: Query feature if an attribute is null after 180 days of date on another attribute?

Anonymous User — Fri, 05 Oct 2018 18:17:12 GMT

Awesome! Thanks for your help on this.

Re: Query feature if an attribute is null after 180 days of date on another attribute?

Anonymous User — Fri, 05 Oct 2018 18:50:31 GMT

I used the code you supplied but noticed it's returning everything 180 days or older. I only want results that fall exactly 180 days from current date.

arcpy.SelectLayerByAttribute_management(permits, "NEW_SELECTION", "Date_Accepted = date '{}' AND Fee_Paid Is NULL".format(notice.strftime("%Y/%m/%d"))) ‍

Re: Query feature if an attribute is null after 180 days of date on another attribute?

JoeBorgione — Sun, 12 Dec 2021 07:45:00 GMT

My bet is to change the < to = and you should be good to go:‍



wc = "Field IS NULL AND DateField = DATE '{}'".format(date_past.strftime("%Y-%m-%d %H:%M:%S"))
‍‍‍‍‍‍‍

Re: Query feature if an attribute is null after 180 days of date on another attribute?

RandyBurton — Sun, 12 Dec 2021 07:45:03 GMT

You could try 'Between' and set hours = '00:00:00' and '23:59:59' (between will get both these values and all those inbetween). Or add another day with timedelta.

dp = date_past.strftime("%Y-%m-%d")

wc = "Field IS NULL AND DateField BETWEEN DATE '{} 00:00:00' AND DATE '{} 23:59:59' ".format(dp,dp))
‍‍‍‍‍‍‍‍‍‍‍

Re: Query feature if an attribute is null after 180 days of date on another attribute?

RandyBurton — Fri, 05 Oct 2018 19:31:21 GMT

When dealing with date/time, I probably wouldn't use "=" in the query. See Joshua Bixby‌'s second comment in this thread with ESRI's explanation.

Re: Query feature if an attribute is null after 180 days of date on another attribute?

JoeBorgione — Fri, 05 Oct 2018 19:42:05 GMT

After I read you post with 'between' I realized that..... thanks!

Re: Query feature if an attribute is null after 180 days of date on another attribute?

RandyBurton — Fri, 05 Oct 2018 19:46:27 GMT

And after I re-read Esri's comment, I started thinking milliseconds. So "23:59:59" could also miss "23:59:59.9".