Archives of the TeradataForum

Message Posted: Mon, 17 Dec 2012 @ 17:20:43 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Under normal circumstances, inserting timestamp data simply requires the right FORMAT statement. However, in Anomy's case ("need to insert data into table but the data is like this format:-- Aug 27 2009 7:07:43:470PM") there are 2 minor items that prevent a simple CAST from working. In the data provided, the first character of the hours is blank, which the normal format won't handle. As well, the separator between the seconds and the fractional seconds is ':', which also causes problems. Both can be chopped out and replaced with characters which match the standard syntax, as in:

     SELECT  'AUG 27 2009  7:07:43:470PM' (NAMED BOB) , SUBSTRING(BOB FROM 1 FOR 12)||CASE
     WHEN SUBSTRING(BOB FROM 13 FOR 1) = ''
     THEN '0' ELSE SUBSTRING(BOB FROM 13 FOR 1) END
     ||SUBSTRING(BOB FROM 14 FOR 7)||'.'||SUBSTRING(BOB FROM 22 FOR 5) (NAMED JOE)
     , CAST (JOE AS TIMESTAMP(3)  FORMAT 'MMMBDDBYYYYBHH:MI:SSDS(3)T' ) (NAMED TOM)

In the event that those 2 offending pieces of stuff were simply typed in, and the actual data is a little better, then the SUBSTRINGs and CASE statements can be skipped and the data can be CAST straightaway.

HTH,

JL

	<Prev	Next>		<<First	<Prev	Next>	Last>>