All Forums Database
kartaj 10 posts Joined 10/15
12 Feb 2016
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

rupert160 131 posts Joined 09/10
13 Feb 2016

Would using a CASE statement skip this problem?

case column

   when is null then null

   else cast(...)

end

 

dnoeth 4628 posts Joined 11/04
13 Feb 2016

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

 

Dieter

dins2k2 51 posts Joined 05/13
15 Feb 2016

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

You must sign in to leave a comment.