Error: OBIEE Prompt showing an error with this Datetime Format

Hemant_Sharma

SELECT  CASE 

 WHEN "dimension"."START_DATE" LIKE '%13-05-2022 17:00:00%' THEN  'Quiz on ABC'

  WHEN "dimension"."START_DATE" LIKE '%30-05-2022 08:00:00%' THEN  'Quiz on XYZ'

  WHEN "dimension"."START_DATE" LIKE '%01-07-2022 14:00:00%' THEN  'Quiz on QRT'

  ELSE  'Quiz Not Found'  END FROM  "quiz" FETCH FIRST 65001 ROWS ONLY

Hi All,

I am using this query in prompt's SQL Result to group data but it is showing this error, may be due to datetime format.

Plz Help.

Thanks in advance

Gianni Ceresa

What data type is your "dimension"."START_DATE" column? Because the LIKE %...% syntax only works on strings, if it's a real date you can't just expect it will work with some magic conversion from date/time to text.

Hemant_Sharma

It is Timestamp in RPD, but how will i compare this timestamp, means what is the format of timestamp for comparing in OBIEE in database start_date is datatype date '28-06-2022 17:00:00' and timestamp in RPD.

pls provide format for comparing like :- "dimension"."START_DATE"='28-06-2022 17:00:00' or something else format

Gianni Ceresa

It's actually a = that you can use for timestamps.

The easier way to get what you are after is to build an analysis in that subject area, and set a filter on that "dimension"."START_DATE" : if you look at the LSQL of the analysis you will get the exact syntax for a = with your timestamp/date.