All Forums Database
john9 30 posts Joined 05/14
15 Jun 2015
time stamp field insert

Hi All -
i am having trouble to insert the 2010-1-10.21.5.59.951939000 value into a time stamp field which is defined as TIMESTAMP(6) FORMAT 'YYYYMMDDBHH:MI:SS.S(F)'.
 
Could you please provide me a solution how to insert this into TIMESTAMP(6) FORMAT 'YYYYMMDDBHH:MI:SS.S(F)'.
 
Thank you very much

dnoeth 4628 posts Joined 11/04
15 Jun 2015

Hi John,
 
in TD14 you can utilize Oracle's TO_TIMESTAMP:

 TO_TIMESTAMP(x, 'YYYY-MM-DD.HH24.MI.SS.FF6')

But both Teradata and Oracle don't support a single digit month, so you need to add the missing zero:

TO_TIMESTAMP(CASE WHEN x LIKE '____-_-%' THEN SUBSTRING(x FROM 1 FOR 5) || '0' || SUBSTRING(x FROM 6) 
                  ELSE x
             END, 'YYYY-MM-DD.HH24.MI.SS.FF6')

 

Dieter

john9 30 posts Joined 05/14
24 Jun 2015

Thanks Dieter i will try this and get back to you if it is not working.

yuvaevergreen 93 posts Joined 07/09
25 Jun 2015
SEL 
'2010-1-10.21.05.59.454545'  AS CH1,
SUBSTR(CH1,1,5)||'0'||SUBSTR(CH1,6) AS CH2,
CAST(CH2 AS TIMESTAMP(6)) AS CH3

 

You must sign in to leave a comment.