Archives of the TeradataForum
Message Posted: Mon, 16 Jan 2006 @ 13:50:12 GMT
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 SecsHowever, 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?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|