Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 04 May 2007 @ 16:42:56 GMT


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


Subj:   Format of timestamp in select
 
From:   Steve Hager

I need to unload timestamp data from Teradata and then compare it to data unloaded from DB2 on OS/390. How do you override the colon (':') delimiter in the time portion of a timestamp data type in a select?

     ct wm_ad_hoc.table1 (TS_column timestamp(6)) ;

     ins wm_ad_hoc.table1 values ('2007-04-02-14.20.34.015629') ;

     SEL TS_COLUMN FROM WM_AD_HOC.TABLE1 ;

                      TS_column
     --------------------------
     2007-04-02 14:20:34.015629

Required results:

     2007-04-02-14.20.34.015629

I understand that I can break the field apart using SUBSTR, but I assumed it can be done with a simple format clause.

     SEL
     (SUBSTR(CAST (TS_COLUMN AS CHAR(26)),1,13)
     ||'.'
     ||SUBSTR(CAST (TS_COLUMN AS CHAR(26)),15,2)
     ||'.'
     ||SUBSTR(CAST (TS_COLUMN AS CHAR(26)),18,9) ) (CHAR(26)) AS TS_COLUMN
     FROM
        WM_AD_HOC.TABLE1;

     TS_COLUMN
     --------------------------
     2007-04-02 14.20.34.015629

Thanks in advance,

Steve



     
  <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: 27 Dec 2016