Calculate date difference in ArcMap

4432
3
Jump to solution
12-04-2014 07:56 AM
JoseSanchez
Occasional Contributor III

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

0 Kudos
1 Solution

Accepted Solutions
JoseSanchez
Occasional Contributor III
0 Kudos
3 Replies
LangdonSanders2
Occasional Contributor

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)

VinceAngelo
Esri Esteemed Contributor

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) 

0 Kudos
JoseSanchez
Occasional Contributor III

The answer is very simple:

MY_FIELD_DATE < CURRENT_DATE - 90

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

0 Kudos