Archives of the TeradataForum

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

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

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>>