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?
 
Kevin CrossConnect With a Mentor Chief 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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.