Hello all
I need help with a date filter. In sql the filter is as such:
where ((zS.Status = 'Active' and zS.ServiceActivationDate <= eomonth(DATEADD(month,-1,getdate())))
OR (zS.Status = 'Cancelled' and zS.CancelledDate > eomonth(DATEADD(month,-1,getdate()))))
How can I add this to a report in Cognos? I have tried everything but it is not returning the results I need as the SQL query is.
thanks in Advance!
That works fine for me. Please provide the filter expression you are trying to use in Cognos.
I was actually able to get it to work using the below filter:
[Status] = 'Active' and [Service Activation Date] <= _add_months(current_date,-1)
or
[Status] = 'Cancelled' and [Cancelled Date] >= current_date
Did you try this?
[Status] = 'Active' and [Service Activation Date] <= eomonth(dateadd({month},-1,getdate()))
or
[Status] = 'Cancelled' and [Cancelled Date] >= eomonth(dateadd({month},-1,getdate()))
yes, they actually worked both ways. Thank you for your help!