Cognos 10 fixed MM/dd but variable year

Question

Want to set July 01 as month and day but have a variable for the year. Always want to default to year of most recent past July 01.

Has to work within the parameters present in Cognos 10.2. Tried several If statements with date2strings and string2date functions. Also tried a series of columns designed to filter data recursively.

logic If current_month <= 6 then year(current_date, -1 ) else year(current_date). Want to concatenate the year with xxxx-06-01 or preferably 06/01/xxxx. Keep getting run-time errors, not errors in Query Calculation.

Can't see the forest for the trees at this point. Continue to explore, will post solution if achieved.

Answer 1

You want to keep dates in date format and avoid conversion to string and concatenation if possible. Cognos gives you several native functions to manipulate dates without having to convert.

Here's your expression:

_add_years(
    _add_months(
        _first_of_month(
            current_date
        ),
        7 - extract(
            month,
            current_date
        )
    ),
    floor(
        extract(
            month,
            current_date
        )
        / 7
    ) - 1
)

It easier to understand if you examine it inside out.

The _first_of_month() function returns the first day of the current month. We then use some math to determine an offset to pass into the _add_months() function that will turn the first of the current month into the first of July in the current year for every possible month value. Lastly, we use math again to either add 0 or 1 years to the resulting date using the _add_years() function. For all months less than 7, we add -1 and for months 7 and greater we add 0.