Topic
4 replies Latest Post - ‏2013-02-14T11:51:07Z by SystemAdmin
SystemAdmin
SystemAdmin
15496 Posts
ACCEPTED ANSWER

Pinned topic Error in the expression to find date in range

‏2013-02-13T11:14:00Z |
i am using cognos10.1 and using below expression in my report query filter in report studio.
here MonthKEY has value from "199301" to "205012"
and i want to display between "201101" and last month of this year for .e.g. "201301" as our present month is "201302"
expression:

MonthKEY >= 201101 and MonthKEY <= cast((cast (extract (year, _add_months (current_date, -1)), varchar(4))||cast (extract (month, _add_months
(current_date, -1)), varchar(4))), integer)

validation this expression show no error but i an not getting the correct result
i did some work around & found there is some problem in half part of the expression
.i.e.
cast (extract (month, _add_months (current_date, -1)), varchar(4))

in the above part expression it should find "01" but no result if i try to replace above half part expression with "01" it shows the correct result as expected

any help will be appreciated
Updated on 2013-02-14T11:51:07Z at 2013-02-14T11:51:07Z by SystemAdmin
  • DanielWagemann
    DanielWagemann
    411 Posts
    ACCEPTED ANSWER

    Re: Error in the expression to find date in range

    ‏2013-02-13T14:34:33Z  in response to SystemAdmin
    Your month is only two digits but you are reserving four digits worth of space. Maybe you are getting two free blanks.... so your value is really '01 ' or ' 01'.

    try chaning the varchar(4) to varchar(2) for the month piece.
    • SystemAdmin
      SystemAdmin
      15496 Posts
      ACCEPTED ANSWER

      Re: Error in the expression to find date in range

      ‏2013-02-14T04:58:04Z  in response to DanielWagemann
      Hi Daniel,

      I tried using varchar(2) as well but no help.
      Just to add one point i tried to run the query as "view tabular data" also but no help no value is getting displayed.

      Any help is appreciable
      • DanielWagemann
        DanielWagemann
        411 Posts
        ACCEPTED ANSWER

        Re: Error in the expression to find date in range

        ‏2013-02-14T11:26:22Z  in response to SystemAdmin
        Can you post the SQL for both the hard coded value that works and the SQL from the scenario that does not.
        • SystemAdmin
          SystemAdmin
          15496 Posts
          ACCEPTED ANSWER

          Re: Error in the expression to find date in range

          ‏2013-02-14T11:51:07Z  in response to DanielWagemann
          Hi Daniel,

          My issue get resolved i replaced the existing expression by

          MonthKEY >= 201101 and MonthKEY <= extract(YEAR, _add_months (current_date, -1))*100+ extract (MONTH, _add_months (current_date, -1))

          and it gives the correct result as expected.:-)
          Thanks for your help