Definition Query- Date field Null

2178
6
02-08-2017 01:48 PM
RickeyFight
MVP Regular Contributor

I want features to disappear when they are 14 days older then the completed date (date field in attribute table). 

The catch is that I do not always know the completed date. In this case the completed date will be NULL. When I do not know the completed date I want to use the Created date (another date field in the attribute table). This created date will always have a date in it.

Is this possible? 

6 Replies
RichardFairhurst
MVP Honored Contributor

If this is a file geodatabase the expression would be:

CURRENT_DATE - COMPLETED < 14 OR (COMPLETED IS NULL AND CURRENT_DATE - CREATED < 14)

The features would disappear if they were completed 14 days before today or had no completed date and were created 14 days before today.  For example, January 25, 2017 would not appear on February 8, 2017, but January 26, 2017 would appear.

RickeyFight
MVP Regular Contributor

I ended up using this 

completed_date  >=DATEADD(day,0,convert(date,GETDATE())) OR created_date >=DATEADD(day,0,convert(date,GETDATE())) 

I get an error with CURRENT_DATE

0 Kudos
RichardFairhurst
MVP Honored Contributor

What is your data source type?  CURRENT_DATE works with File Geodatabases (I tried the SQL on my own data before posting it).  It should also work for a shapefile.  It would not work with a Personal Geodatabase and probably won't work with any Enterprise database (SQL Server, Oracle, etc.).

RickeyFight
MVP Regular Contributor

I have enterprise Database. 

0 Kudos
RichardFairhurst
MVP Honored Contributor

In that case, if you have any further questions about SQL expressions, you have to tell us up front which Enterprise database you are using.  For most sophisticated query expressions the answer you need will completely depend on the enterprise database you are using.

RickeyFight
MVP Regular Contributor

I did not realize I needed too until after you had answered the question.

0 Kudos