Search code examples
cognoscognos-10

Rearranging the content of a cell in Cognos 10

I've got birthdays as a varchar formatted as dd-mm-yyyy and I'm trying to build a filter that shows only the birthdates of the last 7 days.

Now normally I'd convert that field to a date field, but since it contains dummy values this isn't an option.

So I want to change the output to YYYYMMDD so I can use the value in a => filter.

How can I do this?

Solution

  • If you want the result in string format:

    substring([Date],7) || substring([Date],4,2) || substring([Date],1,2)

    ..or in integer format:

    cast(substring([Date],7),integer) * 10000 + cast(substring([Date],4,2),integer) * 100 + cast(substring([Date],1,2),integer)