Archives of the TeradataForum

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


	<Prev	Next>		<<First	<Prev	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>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference