Home Page for the TeradataForum
 
https:

Archives of the TeradataForum

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


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


Subj:   Re: Converting to date format
 
From:   Lunn, Jonathan

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020