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))

13 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.

      • Hey Nanno, I’m working on a cognos report and tryng to collect the number of days in the year. For example if my current date is ’06-21-2018′, it should give the number of days in 2018 which is 365.
        I tried the following
        _day_of_year ([Monthly Deposit Data Mart].[(en) SERV_FEE].[(en) CYC_END_DTE])

        Its giving me the remaining number of days in the year from my cycle date.

        Any suggestions?

        • Hi Shruthi,

          Try and see if below functions work:

          Start of Current Year: Code: trunc({sysdate},’y’)
          End of Current Year: Code: add_months(trunc({sysdate},’y’),12)-1

          Instead of {sysdate} then try to use your own date.

          Next do a calculation: End Date – Start Date and see if this gives you the result you need.

          Regards,
          Nanno

  4. Hi

    Today’s Data has been updated in the database and in my report i need take date filter which only todays data has to display.

    In DataBase date as yyyy-mm-dd 00:00:00

    In the report studio i have written the query as Current_Date which says data is not available.

    Could you please help me with the solution

    • WHEN I RUN THE REPORT, THE RECORDS WHICH ARE ON TODAY’S DATE HAS TO DISPLAY IN THE REPORT WITH THE TIMESTAMP.

    • Hi Krishna,

      What you can do is place all your data in a list. Run the report andere you will see the data.
      Next you can create a new Data Item:
      Trunc (Database Date) name it Date1
      Another Data Item:
      Trunc({sysdate}) name it Date2

      Place both new items in your list. Run the report. Now you should see that both fields have the same date format. And you should see that dates exist where you want to filter on.
      Next create a filter where:
      Date1 = Date2

      Run the report and hopefully the filter works correctly.

      If the trunc function does not work, you could try “Cast …. As Date”

      Good luck.

      Regards,
      Nanno

Leave a Reply

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