Working with dates in Cognos

In Cognos reports I use various dates. I use today, first day of this year, first day of last year and last day of last year.First and last day of last year is used in january. In february we switch to the current year.

I use the following expressions to fill these dates:

Today: current_date

First day of this year: _add_days(Current_date ; (_day_of_year(Current_date)*-1)+1 )

First day of last year (only used in january): _first_of_month(_add_years(current_date;-1))

Last day of last year (only used in january): _last_of_month(_add_months (current_date;-1))

Email this to someoneShare on FacebookShare on LinkedInTweet about this on TwitterShare on Google+Pin on Pinterest

6 thoughts on “Working with dates in Cognos

  1. hey, i need the current day -1.
    i tried ”current_day, -1″ but it is false.

    maybe you can help me ?

    • Use the function _add_days

      _add_days ( date_expression, integer_expression )
      Returns the date or datetime, depending on the format of “date_expression”, that results from adding “integer_expression” days to “date_expression”.

      In the date_expression put: current_date

      Example: _add_days ( 2002-04-30 , 1 )
      Result: 2002-05-01

      Example: _add_days ( 2002-04-30 , -1 )
      Result: 2002-04-29

  2. thanks for your help

    i have one more question.

    for example
    the result of my query is ‘a’.

    but i need the result abc for a.
    if the result is a, so change in abc.

    can you help me
    how i can adjust it.

    sorry my english is not the best

    • Hello Ahmet,

      In your report there is an item which shows “a”, say that item is called [department].
      Drag in a new query item. Put a case in the new item:
      CASE
      WHEN [department] = ‘a’ THEN ‘abc’
      ELSE [department]
      END

      Give the new query item a name, for example “department_case”

      Drag both items in a list and run the report to see the result.

      Regards,

      Nanno

  3. Hi,
    I need the last month. i use _add_months (current_date; -1) but it false.
    Can you help me please?
    Thanks

    • Hi,

      Please use function selector when editing a Data Item Expression. When you select the function _add_months, tips become visible. It says:
      _add_months ( date_expression,integer_expression )

      Example: _add_months(2012-04-15 , 3)
      Result: 2012-07-15

      Please be aware if you use a comma(,) or semicolon(;)

      Sometimes it helps to build a Data Item, part by part. You first start with current_date in a Data Item to see if that works. Then add the _add_months around it and see it that works.

Leave a Reply

Your email address will not be published. Required fields are marked *