2 Replies Latest reply on Feb 17, 2016 7:53 PM by Felipe_Idalgo

    How to use SUBSTRING() for DATETIME column

    Harpreet86
    Harpreet86 Oct 1, 2015 7:17 PM

      Hi,

      I am facing an issue while using SUBSTRING() in OBIEE 11g. My objective is to extract HOUR and "AM/PM" text from DATETIME column and concatenate those to get "HH AM/PM"

      For example: I have a datetime value = "07/25/2015 1:23:39 PM"

      I need to extract "1" from the HH section, and concatenate with "PM" to get "1 PM"

       

      The way I am using SUBSTRING() is:

       

      SUBSTRING("<table_name>"."<DateTime_Column>" FROM 11 FOR 2) || ' ' || SUBSTRING("<table_name>"."<DateTime_Column>" FROM 20 FOR 2)

       

      I am getting an error: Function Substring(argument 1) doesn't support non-text format.

       

      If I want to CAST date into VARCHAR, I am not able to get the TIME which is what I need to extract.

       

      Kindly advice for this requirement, if I am following a correct way or if there's any other better way to achieve this.

       

      Thanks