4 Replies Latest reply: Apr 1, 2013 9:53 AM by Srini VEERAVALLI RSS

    OBIEE Cast as char,timestamp erroring

      I AM ABLE to use the cast as char and then cast as timestamp function on 2 date columns to get the time diffrnc using timestampdiff. This is on an ebs 11 instance database.
      As we are upgrading to EBS R12, we set up a new test instance of obiee on this. But in this new test instance, the same formula is erroring out.
      The date is in format yyyy/mm/dd. After cast as char , i get it in format mm-dd-yyyy in both instances. But on cast as timestamp,in the new (r12) instance i get the error
      Oracle Error code: 1843, message: ORA-01843: not a valid month at OCI call OCIStmtFetch. [nQSError: 17012] Bulk fetch failed. (HY000)
      I tried passing date in the format 'dd-mon-yy' (char format) in the r12 instance and then cast it timestamp and this seems to work. Why is the bi server expecting date in this format after char and how can i change this?
      note:the nls_date_formats are same in both the database.
      the date formats in both the nqsconfig.ini files are also same

      any other place where i should set this?