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