Date functions not working inside of calculated field, but working in filter?

[psrpsrpsr]

I have a report that runs on the 18th of each month and reflects data as of end-of-day on the 17th. I am retrofitting the report to be more troubleshooting-friendly by setting the filters up with CASE statements to lock the report into place on the 17th. For example, each query has a filter like this, which runs the queries for the prior month if yesterday was less than the 17th of the month, and this month if yesterday was the 17th or after:

[Date] = (
CASE
WHEN extract(day,current_date-1) < 17
THEN _add_months(_add_days(_first_of_month(current_date-1),16),-1)
ELSE  _add_days(_first_of_month(current_date-1),16)
END)

PROBLEM: I have a query that produces a formatted date field for a report: extract (month, current_date) || extract (year, current_date). Now, when I try to achieve the same conditional display of the date depending on the day of the month on which the report is run, I get the following error messages (below field example):

[Month] =
CASE
WHEN extract(day,current_date-1) < 17
THEN _add_months(_add_days(_first_of_month(current_date-1),16),-1)
ELSE  _add_days(_first_of_month(current_date-1),16)
END

The data types 'date' and smallint are incompatible in the subtract operator.
OR
The data types 'varchar(3)' and date are incompatible in the searchedcase operator.

Why is this? Why can't I use date functions inside of a field case statement? Why does this work when using filters, but not within a calculated field itself?

Thanks all

[AnalyticsWithJay]

I assume you're using DQM because CQM would allow this operation to happen. Cognos DQM is strict on syntax (which is a good thing).

Your issue is because of current_date - 1. The message about incompatible types in the operator is because it's trying to subtract an integer from a date. You'll now have to use syntax such as:

Code Select Expand


extract(day,_add_days (current_date, - 1))
...
...


[psrpsrpsr]

Thanks CognoidJay - how come this works in filters but not in query calculations?

[AnalyticsWithJay]

Thanks CognoidJay - how come this works in filters but not in query calculations?

Wish I could give you a good answer, but it shouldn't work in some places and not in others. If you're going to force your users to use strict syntax then you should be forcing it everywhere.