Clarity

If you have a query base portlet and a date in it, you just set the date as a filter field and specify that to be date range.

It will filter the items that do have the date in that range.

How do do the opposite?

How do you create a filter which returns the items that do not have a date in that range ie blank or other date?

I could think that using parameters I would need two which would give two date boxes which would appear to the user somewhat similar as the date displayed as a range so that it would not bee too different.

Is there a way to do that without parameters just using the date field?

Does not look promising for a simple solution other than two parameters as in trace using the date range filter is applied  like this

      and niku.cmn_trunc_date_fct(prtask.prStart) >= niku.cmn_trunc_date_fct(?)
      and niku.cmn_trunc_date_fct(prtask.prStart) <= niku.cmn_trunc_date_fct(?)

Yeah I agree - don't think that is a way to get your logic without two separate user-parameter fields.

The "date range" display of a filter field only works against returned column data as far as I know, so I agree with all your comments.

--

Is it possible to turn your logic around though ; obviously I don't know the use-case at all but can your "excluded date range" be expressed in meaningful language instead of explicit dates - for example "only show current period" / "only last 3 months data" / "do not show completed transaction dates" and so on - you could then make a filter field (user parameter with a custom lookup) on those values and code the relevant date logic into the NSQL based upom teh parameter.

(just trying to suggest an alternative user-experience if you are going to get push-back on the two date fields)

Painfully established a where clause with two parameters which works.

Does not look exactly the same as two parameters meant to me two filter fields so the date pickers are on two separate rows instead of one as in date range.

There are other options though...

A Single-Parameter Date Range in SQL Server Reporting Services - SQLServerCentral 

if you want more pain.

and the WHERE clause

((@WHERE:PARAM:USER_DEF:DATE:z_start@ is null and @WHERE:PARAM:USER_DEF:DATE:z_finish@ is null)

or

(@WHERE:PARAM:USER_DEF:DATE:z_start@ is null and @WHERE:PARAM:USER_DEF:DATE:z_finish@ <= my_date)

or

(@WHERE:PARAM:USER_DEF:DATE:z_start@ >= my_date and @WHERE:PARAM:USER_DEF:DATE:z_finish@ is null)

or

(@WHERE:PARAM:USER_DEF:DATE:z_start@ >= my_date or @WHERE:PARAM:USER_DEF:DATE:z_finish@ <= my_date)

or (my_date is null))

Can simplify that NSQL a bit I think;

...

AND ( my_date BETWEEN @NVL@( @WHERE:PARAM:USER_DEF:DATE:z_start@ , my_date) AND @NVL@( @WHERE:PARAM:USER_DEF:DATE:z_finish@ , my_date) 

OR ( my_date is null )

)

...

Thanks

I guess you can figure shorter NSQL, but for me what I posted was faster to achieve something that works.

I was thinking using NVL, but thinking differently so that if the first date expression  returned null what should the data type an value be so that it would work - blank date, 1=1 or something else.

It was supposed to be NOT BETWEEN, does that do it?

oops yes sorry, NOT BETWEEN