char(23) to timestamp(6)

Database
Enthusiast

char(23) to timestamp(6)

Hi Team,

I have a requirement to convert the data from char(23) to timestamp(6), while casting the column, i get the error as invalid timestamp

select CAST(DW_PKG_UPD_DTS AS TIMESTAMP(6) FORMAT 'YYYY-MM-DD-HH.MI.SS.S(6)') from FIN_SVC_PKG.ETL_MTRC_TIE_OUT_RULES

Some columns are null, it should be as null only, the columns which have data should be in timestamp(6)

sample data
2012-05-03 01:52:54
2012-07-29 11:04:59
2013-02-26 04:08:00
20150528 11:47:34
20120629 08:01:39
20150429 12:13:03
2013-09-04 02:48:44
20120315 05:11:48
20121103 11:53:32
Tags (1)
3 REPLIES
Enthusiast

Re: char(23) to timestamp(6)

Would using a CASE statement skip this problem?

case column

when is null then null

else cast(...)

end
Senior Apprentice

Re: char(23) to timestamp(6)

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:

CASE
WHEN x LIKE '____-__-__ %'
THEN CAST(x AS TIMESTAMP FORMAT 'yyyy-mm-ddBhh:mi:ss')
ELSE CAST(x AS TIMESTAMP FORMAT 'yyyymmddBhh:mi:ss')
end
Enthusiast

Re: char(23) to timestamp(6)

Vinay,

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,
CASE
WHEN date1 is null then null
ELSE (
CASE
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
)
END as Formated_Date

HTH.

Thanks,

Dinesh