Converting Seconds to Time Stamp HELP

Posted on 2008-11-06
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
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 ?

Question by:EHardie
    LVL 59

    Accepted Solution

    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. :)
    LVL 59

    Expert Comment

    by: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

    Author Closing Comment

    I was being an idiot thanks !
    LVL 12

    Expert Comment

    There is nothing wrong with your calculation.
    If you don't want to include the days (Hours > 24) then do this

    SET @tmpSECONDS_LOG=462002                                          
    DECLARE @tmpDAYS int
    DECLARE @tmpHOUR int
    DECLARE @tmpMIN 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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now