6 Replies Latest reply on Nov 22, 2012 4:18 PM by Robert Angel

    Timestamp different behaviour

    Robert Angel

      I have a varchar field in my repository which holds values like 'January 2010'.

      If I extract from this, using formulas, '01-Jan-10' then I can cast the result as a timestamp.

      However, if I try to cast a string literal '01-Jan-10' to timestamp it errors telling me the format is incorrect.

      Similarly if I try to cast a presentation variable holding the value '01-Jan-10' as timestamp I get the same error.

      Why is the behaviour different for literals / presentation variables than for data extracted?

      How can I get around this problem such that my presentation variable and / or literal will happily convert to a timestamp.

      -- additional information

      When I employ the following logic the timestamp conversion works fine, but if I remove the case statement which will never evaluate to be true then the error occurs.

      So this code would work for me (but as it would have to run against the DB for every value would be very inefficient)
      case when "Time"."Month Start Date" is null then "Time"."Month Start Date" else cast('@{P_DATE}{01-JAN-10}' as timestamp) end
      But this would error (ceteris parabus)
      cast('@{P_DATE}{01-JAN-10}' as timestamp)
      My guess on this is that including the Month Start Date (timestamp) column somehow 'helps' OBIEE resolve that the result for this column should be a timestamp, but there must be a better workaround for this than what I have found, as I want this conversion for a filter based on a presentation variable.

      Anyone help please, and not evaluate as I have sqlserver as my DB in this case.



      Edited by: Robert Angel on Nov 22, 2012 7:34 AM to add details