topic Re: Calculate date difference in ArcMap in Geoprocessing Questions

Calculate date difference in ArcMap

JoseSanchez — Thu, 04 Dec 2014 15:56:59 GMT

Hi all

How can I calculate the difference between today and a date field in ArcMap in days.

I want to select all the records that were created during the last 90 days.

Regards

Re: Calculate date difference in ArcMap

LangdonSanders2 — Thu, 04 Dec 2014 16:24:54 GMT

Hello,

You will access the system date in your calculation and use the DateDiff function. The syntax will depend on the underlying data source, Oracle, SQL Server, etc.. Example: DATEDIFF(day, date_col ,SYSDATETIME()) < 90.

I found the answer in this post by Vince Angelo‌

ArcSDE supports five different RDBMSes that use SQL, all of which have different syntax
for referring to dates and date functions. Even using just Microsoft SQL-Server, there
are multiple possible flavors (2005, 2008, 2008R2). Dates can also be difficult due to
variability of precision -- does a time reference to a day without hour imply midnight
or noon, and in what timezone?
What you're looking for probably involves the SYSDATETIME() function and some variant
of DATEADD or DATEDIFF --
where date_col > DATEADD(day,-7,SYSDATETIME())
or
where DATEDIFF(day,date_col,SYSDATETIME()) < 7
but that depends on the exact types involved. This documentation page is probably a
good starting point, but you'll likely need to experiment, since different variants of the
same expression are likely to give different performance, depending on how the index
interacts with the expression.
- V

ArcGIS Help on SQL Query (10.2, 10.2.1, and 10.2.2)

Re: Calculate date difference in ArcMap

VinceAngelo — Thu, 04 Dec 2014 16:35:52 GMT

I updated the invalid link in the original post so it goes to the intended page:

Date and Time Data Types and Functions (Transact-SQL)

Re: Calculate date difference in ArcMap

JoseSanchez — Thu, 04 Dec 2014 18:05:06 GMT

The answer is very simple:

MY_FIELD_DATE < CURRENT_DATE - 90

https://community.esri.com/message/442826?et=watches.email.thread#442826