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
The answer is very simple:
MY_FIELD_DATE < CURRENT_DATE - 90
https://community.esri.com/message/442826?et=watches.email.thread#442826
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)
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 syntaxfor referring to dates and date functions. Even using just Microsoft SQL-Server, thereare multiple possible flavors (2005, 2008, 2008R2). Dates can also be difficult due tovariability of precision -- does a time reference to a day without hour imply midnightor noon, and in what timezone?What you're looking for probably involves the SYSDATETIME() function and some variantof DATEADD or DATEDIFF --where date_col > DATEADD(day,-7,SYSDATETIME())or where DATEDIFF(day,date_col,SYSDATETIME()) < 7but that depends on the exact types involved. This documentation page is probably agood starting point, but you'll likely need to experiment, since different variants of thesame expression are likely to give different performance, depending on how the indexinteracts with the expression.- V
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)
Angemeldete Mitglieder können Beiträge verfassen, Updates folgen und mehr. Neu hier? Registriere ein kostenloses Konto.
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.
