You are here: > ESRI Forums > arcgis desktop discussion forums > Thread Replies

ArcGIS Desktop Discussion Forums

ArcGIS Desktop - ArcMap Layers and Symbology forum

Using CURRENT_DATE function in Definition Q...   Peter Whitworth Aug 26, 2009
Re: Using CURRENT_DATE function in Definiti...   Henry Kovacs Aug 26, 2009
Re: Using CURRENT_DATE function in Definiti...   Peter Whitworth Aug 26, 2009
Re: Using CURRENT_DATE function in Definiti...   Dan McCoy Sep 29, 2009
Re: Using CURRENT_DATE function in Definiti...   Daniel Johns Oct 30, 2009
Re: Using CURRENT_DATE function in Definiti...   Henry Kovacs Oct 30, 2009
Re: Using CURRENT_DATE function in Definiti...   Daniel Johns Oct 30, 2009
Re: Using CURRENT_DATE function in Definiti...   Daniel Johns Oct 30, 2009
Re: Using CURRENT_DATE function in Definiti...   Eric Peterson Jan 29, 2010
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Using CURRENT_DATE function in Definition Query 
Author Peter Whitworth 
Date Aug 26, 2009 
Message I'm trying to set up a layer based on a Definition Query in ArcMap to include only features that have a date attribute value that is within the last week.
I've tried MY_FIELD_DATE > CURRENT_DATE - 7 but this doesn't verify('An invalid SQL statement was used')
Can anyone suggest the appropriate SQL. I'm using an Oracle 10G geodatabase. 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Using CURRENT_DATE function in Definition Query 
Author Henry Kovacs 
Date Aug 26, 2009 
Message Shouldn't you be using a less than symbol, not greater then?

The query would then be MY_FIELD_DATE < CURRENT_DATE - 7

Hopefully, that should give you what you need.

H. 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Using CURRENT_DATE function in Definition Query 
Author Peter Whitworth 
Date Aug 26, 2009 
Message Hi Henry

I think > is correct, but I don't think this is the problem. The SQL doesn't verify so there must be something else wrong. The following query (Select CURRENT_DATE - 7 FROM Dual) run directly in Oracle returns the date 7 days ago so I can't see why this doesn't work in a Definition Query. 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Using CURRENT_DATE function in Definition Query 
Author Dan McCoy 
Date Sep 29, 2009 
Message Does this work...?

MY_FIELD_DATE >= CURRENT_DATE - 7
 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Using CURRENT_DATE function in Definition Query 
Author Daniel Johns 
Date Oct 30, 2009 
Message I was having a similar issue. I wanted to select all records 30 days prior to today's date, but it was including all of the records greater than my current date. So I did the following:

DATEDIFF(d, [DateField], GETDATE()) <= 30 AND NOT [DateField] > GETDATE()

It went X days back and then said NOT to include records where the [DateField] is greater than the current date AKA GETDATE()

Hope this helps,
Daniel 
  Daniel Johns
GIS Manager
Clay County Utility Authority
GIS Corner - www.giscorner.com (djkmog)
 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Using CURRENT_DATE function in Definition Query 
Author Henry Kovacs 
Date Oct 30, 2009 
Message What does the 'd,' represent? 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Using CURRENT_DATE function in Definition Query 
Author Daniel Johns 
Date Oct 30, 2009 
Message d for day, y for year, etc..

http://msdn.microsoft.com/en-us/library/ms189794(SQL.90).aspx

The link above has some info on DATEDIFF 
  Daniel Johns
GIS Manager
Clay County Utility Authority
GIS Corner - www.giscorner.com (djkmog)
 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Using CURRENT_DATE function in Definition Query 
Author Daniel Johns 
Date Oct 30, 2009 
Message DATEDIFF(d, GETDATE(), [DateField]) <= 30 AND NOT [DateField] < GETDATE()

Returns no values less than the current date only the next 30 days.

It's different then the last query I posted which was for the previous dates.

Good luck,
Daniel 
  Daniel Johns
GIS Manager
Clay County Utility Authority
GIS Corner - www.giscorner.com (djkmog)
 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Using CURRENT_DATE function in Definition Query 
Author Eric Peterson 
Date Jan 29, 2010 
Message I just got through a similar problem that involved NULL values in the date field. Try adding something like " AND [field] is not null" to the definition query. See also http://support.esri.com/index.cfm?fa=knowledgebase.techArticles.articleShow&d=22132 
  Eric B. Peterson, Ph.D.
Data Steward, and Vegetation Ecologist
Trinity River Restoration Program
http://www.trrp.net