topic Re: Definition Query- Date field Null in Mapping Questions

Definition Query- Date field Null

RickeyFight — Wed, 08 Feb 2017 21:48:21 GMT

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?

Re: Definition Query- Date field Null

RichardFairhurst — Wed, 08 Feb 2017 23:42:28 GMT

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.

Re: Definition Query- Date field Null

RickeyFight — Wed, 08 Feb 2017 23:55:30 GMT

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

Re: Definition Query- Date field Null

RichardFairhurst — Thu, 09 Feb 2017 00:01:20 GMT

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.).

Re: Definition Query- Date field Null

RickeyFight — Thu, 09 Feb 2017 00:13:05 GMT

I have enterprise Database.

Re: Definition Query- Date field Null

RichardFairhurst — Thu, 09 Feb 2017 00:25:44 GMT

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.

Re: Definition Query- Date field Null

RickeyFight — Thu, 09 Feb 2017 00:42:13 GMT

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