2 Replies Latest reply: Sep 14, 2011 1:51 PM by 858440 RSS

    How to convert varchar to date?

    538039
      Hello everyone, I'm new to OBIEE and I'm running into a roadblock...

      I have a date that is stored in a varchar2 column in the physical table, like so: 20071225
      Silly I know, but that's what I'm dealing with.

      Anyway, I want to create a logical column in the business model (using the Administration tool) and convert it to a date so that I can do date arithmetic with it, but TO_DATE doesn't seem to be supported.

      In Oracle SQL would do this (I'm using XE):
      SELECT SYSDATE - TO_DATE('20050101', 'YYYYMMDD') FROM DUAL

      Is there any way to accomplish this same thing in the OBIEE? I see the CAST function, but there doesn't seem to be any way to cast a string to a date?