Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 05 Jan 2007 @ 09:58:29 GMT

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

Subj:   Re: Casting integer as Time
From:   Victor Sokovin

I have to add that the above was tested only with the ODBC driver for which the Date/Time Format was set to 'III'. Even then one CAST was not enough.

As other posters already verified the first CAST does not work in BTEQ. Without the ODBC help (or cheat, or whatever your attitude towards it is) we have to take care about the leading zeros and colons ourselves. FORMAT is then pretty much the only reasonably easy option. The following seems to work for me:

     insert into tbl_event ( Event_Start_Tm )
     sel substring(73000  (format '999999') (char(6)) from 1 for 2) || ':' ||
     substring(73000  (format '999999') (char(6)) from 3 for 2) || ':' ||
     substring(73000  (format '999999') (char(6)) from 5 for 2)

I hope this answers the question in the original posting. Maybe just a recommendation to go with it: try to avoid such conversions in your database. Either go for Teradata timestamps or store the data in one fixed format. This thread shows once again how much confusion the subject may cause.



  <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: 15 Jun 2023