topic Re: Data Definition to find rows from past 90 days in Data Management Questions

Data Definition to find rows from past 90 days

BenVan_Kesteren1 — Thu, 07 Dec 2017 23:19:32 GMT

Hey,

I am using ArcMap 10.5 with a table within our Enterprise SDE that has a join to a view (also in the SDE). This view has a date field that I would like to use to filter my data on. I need to find rows that were inspected in the past 90 days.

INS_DATE field properties:

An sample of my data:

tbl1.ObjectID	tbl1.AssetID	tbl2.AssetID	tbl2.INS_DATE
1	10000	10000	3/02/2016 11:34:52 AM
2	10001	10000	29/11/2017 8:37:12 AM
3	10002	10000	29/11/2017 1:45:23 PM

So I want to limit my rows to ones that have an INS_DATE within the past 90 days.

I have been trying to use the below code (and about a dozen variables of it) all give a non-descriptive error:

SDE_SPATIAL.GISADMIN.%AUDITS_LATEST_RECORD.INS_DATE > (GetDate() - 90)‍‍‍‍‍‍‍‍

Is there another way I should be approaching this one?

NathanDuncan‌ FYI

Cheers

Ben

Re: Data Definition to find rows from past 90 days

RichardDaniels — Fri, 08 Dec 2017 00:40:16 GMT

Are you sure the tbl2_INS_Date a date field? Also if you need to be exact you should look at what date you are getting (i.e., UTC vs. Local).

If you are working against a sql database you may need to use DATEDIFF() in the WHERE statement of your Select (e.g., in Select by Attribute window)

DATEDIFF(day, INS_DATE, GETDATE()) < 91

This works with ArcGIS Desktop 10.5.1 and SQL 2014 database.

rich

Re: Data Definition to find rows from past 90 days

BenVan_Kesteren1 — Fri, 08 Dec 2017 00:46:26 GMT

Hi Richard,

I included a screenshot of my INS_DATE field, i do see it is a DATE type, is there any way it may not be storing dates?

I don't need to be exact with the 90 days, if I am within 24hr each side of that it will not matter for this particular job.

See below screenshot from when I tried using the DATEDIFF method:

Cheers

Re: Data Definition to find rows from past 90 days

BenVan_Kesteren1 — Sat, 11 Dec 2021 18:54:42 GMT

im not sure if it matters, but I'm thinking it could, the AUDITS_LATEST_RECORD is a joined view. It is joined to the ALBURYTREES via this setup:

SELECT *
FROM 
ALBURYTREES LEFT JOIN
AUDITS_LATEST_RECORD ON ALBURYTREES.ASSET_ID = AUDITS_LATEST_RECORD.ASSET_ID‍‍‍‍‍‍‍

Also just looking at the INS_DATE field, I have confirmed it is of type 'DATE', but you will note the formatting within switches between date only, to datetime.

Is there a way I can cast everything to be a date first, and then find rows where date is from last 90 days?

Note I cannot modify the actual data to do this, it must be done in the Definition Query...