Converting Seconds to Time Stamp HELP

I have a field called ActualDuration which contains an Int value of seconds.
I have used the following select to convert it into a timestamp however I have noticed that the seconds value on my time stamp is not coming out correctly.

This is the select :

SELECT ActualDuration as ActualDuraion_in_seconds,
Cast((ActualDuration/3600) As nvarchar(3)) + ':' + RIGHT('0'+Cast(((ActualDuration % 3600)/60) As nvarchar(2)), 2) + ':' + RIGHT('0'+Cast(((ActualDuration % 3600) % 60) As nvarchar(2)),2) as Time
from SITE_DUR_Billings1
where
SITEID = 15140

Results are :

ActualDuraion_in_seconds      Time
56676                                           15:44:36
30756                                           8:32:36
462002                                          128:20:02
429910                                          119:25:10
883                                          0:14:43

My problem is that if you take 462002 seconds on a calculator and /60 /60 to get time the result should be 128:33

Please can someone tell me why my select is wrong and the seconds part is giving me a different value of 128:20 in the result ?

Thanks
EHardieAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
128.33 is actually what you get on a calculator and that means 128 hours and 1/3 of an hour which is 20 minutes. :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin CrossChief Technology OfficerCommented:
128:20:02 is in HH:MM:SS notation!

If you just want the 128.33, then you can reduce the SELECT to this:

SELECT ActualDuration as ActualDuraion_in_seconds,
ActualDuration * 1.0/3600 as Time
from SITE_DUR_Billings1
0
EHardieAuthor Commented:
I was being an idiot thanks !
0
DimitrisSenior Solution ArchitectCommented:
There is nothing wrong with your calculation.
If you don't want to include the days (Hours > 24) then do this



DECLARE @tmpSECONDS_LOG int
SET @tmpSECONDS_LOG=462002                                          
 
DECLARE @tmpDAYS int
DECLARE @tmpHOUR int
DECLARE @tmpMIN int
DECLARE @tmpSEC INT
 
SET @tmpDAYS = ISNULL(@tmpSECONDS_LOG / (3600 * 24) ,0)
SET @tmpHOUR = ISNULL((@tmpSECONDS_LOG - @tmpDAYS * 3600 * 24) / 3600,0)
SET @tmpMIN = ISNULL(( @tmpSECONDS_LOG - @tmpDAYS * 3600 * 24 - @tmpHOUR * 3600) / 60,0)
SET @tmpSEC = @tmpSECONDS_LOG - @tmpDAYS * 3600 * 24 -@tmpHOUR * 3600 - @tmpMIN * 60
SELECT @tmpDAYS as [Days], @tmpHOUR as [Hours], @tmpMIN as [Minute] , @tmpSEC as [Seconds]

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.