Hopefully this is done during load, you should store timestamps in a string.
If you got different formats you must a CASE to match each possible format:
WHEN x LIKE '____-__-__ %'
THEN CAST(x AS TIMESTAMP FORMAT 'yyyy-mm-ddBhh:mi:ss')
ELSE CAST(x AS TIMESTAMP FORMAT 'yyyymmddBhh:mi:ss')
The below query will give you the output for your requirements. Null as null, given char to timestamp.
You need to manually convert this 20120629 08:01:39 to 2012-06-29 08:01:39. CAST doesn't do it, thats why you got the invalid timestamp error.
Sel '20150528 11:47:34' as date1,
WHEN date1 is null then null
WHEN REGEXP_INSTR(COALESCE(date1, '9999-99-99') , '-', 1, 1, 0, 'c') > 0 THEN CAST(date1 AS TIMESTAMP(6) FORMAT 'YYYY-MM-DD-HH.MI.SS.S(6)') -- Used COALESCE to convert null into a string value.
WHEN REGEXP_INSTR(COALESCE(date1, '9999-99-99') , '-', 1, 1, 0, 'c') = 0 THEN CAST(SUBSTR(date1 , 1 , 4)||'-'||SUBSTR(date1 , 5 , 2)||'-'||SUBSTR(date1 , 7 , 2)||' '||SUBSTR(date1 , 10 , 8) AS TIMESTAMP(6) FORMAT 'YYYY-MM-DD-HH.MI.SS.S(6)')
END as Formated_Date