Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Converting Seconds to Time Stamp HELP

Posted on 2008-11-06
4
Medium Priority
?
1,074 Views
Last Modified: 2010-04-21
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
0
Comment
Question by:EHardie
  • 2
4 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 22894642
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22894661
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
 

Author Closing Comment

by:EHardie
ID: 31513882
I was being an idiot thanks !
0
 
LVL 12

Expert Comment

by:Dimitris
ID: 22894862
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question