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

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

    Re: Error in the expression to find date in range

    ‏2013-02-13T14:34:33Z  
    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

    Re: Error in the expression to find date in range

    ‏2013-02-14T04:58:04Z  
    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.
    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

    Re: Error in the expression to find date in range

    ‏2013-02-14T11:26:22Z  
    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
    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

    Re: Error in the expression to find date in range

    ‏2013-02-14T11:51:07Z  
    Can you post the SQL for both the hard coded value that works and the SQL from the scenario that does not.
    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