Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Mon, 16 Jan 2006 @ 13:50:12 GMT

  <Prev Next>  
Next> Last>>  

Subj:   Time calculations and MLoad
From:   Simon Bloomer

Hi all

I have an input field in a multiload which has as a Time field the number of seconds since midnight. ie, 43200 would be 12:00:00pm and 86399 would be 23:59:59.

I need to convert this to a time that multiload can insert into the target.

The following code works ok to extract out the different components of the time:

     select  cast (86398/3600 as DECIMAL(2)) as Hrs
              , cast ((86398/60 mod 60) as DECIMAL(2)) as Mins
              , cast ((86398/60.0000  mod 1  *  60) as DECIMAL(2)) as Secs
However, I am having a little difficulty putting them back together in the correct format ie HH:MM:SS or HHMMSS because of leading zeros (or absence thereof) where hrs, mins and seconds are all less than 10.
     SELECT  cast (cast (86341/3600 as DECIMAL(2)) as CHAR(2))
             || ':' ||
             cast (cast ((86341/60 mod 60) as DECIMAL(2)) as CHAR(2))
             || ':' ||
             cast (cast ((86341/60.0000  mod 1  *  60) as DECIMAL(2)) as CHAR(2))

Gives me the output

     23:59:1. (note trailing period) when it should read 23:29:01

Does anybody have any ideas or a better example how to do this?

Many thanks

Simon Bloomer

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