Error: OBIEE Prompt showing an error with this Datetime Format

Question

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 · Answer

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.

Hemant_Sharma · Answer

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 · Answer

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.