asked on

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

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kevin Cross

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

EHardie

ASKER

I was being an idiot thanks !

Dimitris

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