Date arithmetic in Cognos 8

Question

What I need is a data item expression which outputs the starting date of the current quarter of last year. Finding the current year is easy, as is subtracting 1 year from that date. But beyond that I get stuck.

I currently have an ugly if expression for each quarter like:

if (extract(month,current_date) in (10,12,12)) then ((extract(year,_add_years (current_date,-1))||'-10-01'))

But no matter what I do I can't concatonate the year and date into string I can convert to a date object. The above code gives the error:

The operation "add" is invalid for the following combination of data types: "integer" and "character"

Trying to cast the integer as a character using cast() I get this error. I also get this error when trying to turn a character array into a date:

The operation "condexp" is invalid for the following combination of data types: "character" and "integer"

Trying to use SQL Server specific functions (it is a SQL Server database) just gives me an error that those functions are unavailable for local processing, so I can't seem to use SS date arithmatic, and I can't find anything particularly applicable in Cognos' built in date functions.

How can I manipulate a date to add a year to a known day/month combination and use that as a date object?

Answer 1

I would go with SQLSERVER built-in functions tht exists in Cognos.
Here is an expression that works for me:

DATEADD({month},(3)*((DATEPART({quarter},[FullDateAlternateKey]))-1),
DATEADD({YEAR}, DATEDIFF({YEAR}, 0, dateadd({year},-1,[FullDateAlternateKey])), 0))

The FullDateAlternateKey field is my date field (from ADVERTUREWORKS DW DB).
If you still have problems, try to isolate the problem by trying this expression on new simple list report.

Answer 2

I'm using cognos 10.1.1, but it would work.

You can get the value by using the following code:

_make_timestamp(
  extract(year, _add_years(current_date, -1)),
  (floor((extract(month,current_date)-1)/3)*3+1),
  01
)

The following is the simple way to get the starting month of the quarter.

(floor((extract(month,current_date)-1)/3)*3+1),

and you can convert timestamp to date or string.

cast(
  _make_timestamp(
  extract(year, _add_years(current_date, -1)),
  (floor((extract(month,current_date)-1)/3)*3+1),
  01
  ),
  date
)

,

cast(
  cast(
    _make_timestamp(
      extract(year, _add_years(current_date, -1)),
      (floor((extract(month,current_date)-1)/3)*3+1),
      01
    ),
    date),
    varchar(10)
)

plus, you can use extract function when you get a day value.

extract(day, _first_of_month (current_date))