time stamp field insert

Database
Enthusiast

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

3 REPLIES
Senior Apprentice

Re: time stamp field insert

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')
Enthusiast

Re: time stamp field insert

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

Enthusiast

Re: time stamp field insert

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