How to verify and validate correct date formats in OTBI where the column is supporting references.
Summary:
We have a scenario where we are using a supporting reference column and casting it as date.
But due to multiple formats of date being entered in the column with different date formats,the report errors out.
Code Snippet (add any code snippets that support your topic, if applicable):
CAST(CASE WHEN "Supporting References Without Balances"."Supporting Reference Name 31" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 31" WHEN "Supporting References Without Balances"."Supporting Reference Name 32" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 32" WHEN "Supporting References Without Balances"."Supporting Reference Name 33" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 33" WHEN "Supporting References Without Balances"."Supporting Reference Name 34" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 34" WHEN "Supporting References Without Balances"."Supporting Reference Name 35" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 35"