how to convert seconds to time in sql

  • hi,

    Could you please give any formula for that to convert seconds to time...

    Thanks,

    Giri

  • Yes sure ! ( A + B )2 = A2 + B2 + 2AB! 😀

    On the other hand, convert to time = what?? time can be specified in hours, minutes, nano seconds, milli second et all.. what do u exactly want?

  • dastagiri16 (3/14/2012)


    hi,

    Could you please give any formula for that to convert seconds to time...

    Thanks,

    Giri

    SELECT DATEADD(ss,someseconds,0)

    That will give you a date/time and you probably won't like the looks of it but it's the first step. Will your seconds at up to less than 24 hours?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • What we are trying to say is that we need more information. To convert seconds to time we need to know the basis of the seconds. Is the number of seconds since midnight? From 1/1/1980?

  • Assuming you mean time as something like HH:MM:SS, this may work:

    DECLARE @seconds INT

    SELECT @seconds = 500

    SELECT RIGHT('00'+CAST(@seconds/3600 AS VARCHAR), 2) + ':' +

    RIGHT('00'+CAST(@seconds/60 AS VARCHAR), 2) + ':' +

    RIGHT('00''00'+CAST(@seconds % 60 AS VARCHAR) , 2) AS [HH:MM:SS]


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • To get time as HH:MM:SS from seconds, you don't need to calculates hours, minutes and seconds, format and concatenate them separately. You can just use one of standard CONVERT options:

    DECLARE @seconds INT

    SELECT @seconds = 500

    SELECT CONVERT(VARCHAR, DATEADD(second,@seconds,0),108)

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Everyone is guessing at what you need. How about telling us what you really are trying to accomplish.

  • Sorry... post withdrawn... posted code for the wrong problem.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Rats! Eugene posted:

    To get time as HH:MM:SS from seconds, you don't need to calculates hours, minutes and seconds, format and concatenate them separately. You can just use one of standard CONVERT options:

    DECLARE @seconds INT

    SELECT @seconds = 500

    SELECT CONVERT(VARCHAR, DATEADD(second,@seconds,0),108)

    I was wondering if CONVERT had a feature for this but I was too lazy to look it up!


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • hi,

    Thanks for replying ...

    It is not working in all cases suppose the seconds is more than 100 hours its showing wrong .

    Thanks,

    Dastagiri.

  • dastagiri16 (3/24/2012)


    hi,

    Thanks for replying ...

    It is not working in all cases suppose the seconds is more than 100 hours its showing wrong .

    Thanks,

    Dastagiri.

    so...yo want elapsed time converted to years:months:hours:minutes:seconds, and not converted to datetime at all, then right?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell (3/24/2012)


    dastagiri16 (3/24/2012)


    hi,

    Thanks for replying ...

    It is not working in all cases suppose the seconds is more than 100 hours its showing wrong .

    Thanks,

    Dastagiri.

    so...yo want elapsed time converted to years:months:hours:minutes:seconds, and not converted to datetime at all, then right?

    Or do you just want hours, minutes, and seconds even if we go over 24 hours?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Lowell,

    could you please give me suggestion for..

    if seconds=999999

    then we can convert to time the result must be 277:46:39(hr:min:sec) ..

    like that the earlier mail mentioned formula

    DECLARE @seconds INT

    SELECT @seconds = 500

    SELECT RIGHT('00'+CAST(@seconds/3600 AS VARCHAR), 2) + ':' +

    RIGHT('00'+CAST(@seconds/60 AS VARCHAR), 2) + ':' +

    RIGHT('00''00'+CAST(@seconds % 60 AS VARCHAR) , 2) AS [HH:MM:SS]

    this is work for that...

    Thanks,

    Dastagiri

  • dastagiri16 (3/25/2012)


    Lowell,

    could you please give me suggestion for..

    if seconds=999999

    then we can convert to time the result must be 277:46:39(hr:min:sec) ..

    like that the earlier mail mentioned formula

    DECLARE @seconds INT

    SELECT @seconds = 500

    SELECT RIGHT('00'+CAST(@seconds/3600 AS VARCHAR), 2) + ':' +

    RIGHT('00'+CAST(@seconds/60 AS VARCHAR), 2) + ':' +

    RIGHT('00''00'+CAST(@seconds % 60 AS VARCHAR) , 2) AS [HH:MM:SS]

    this is work for that...

    Thanks,

    Dastagiri

    Now that we finally know which format you want the result to be, the problem solution becomes very simple if we let SQL Server do most of the work.

    DECLARE @Seconds INT;

    SELECT @Seconds = 999999;

    SELECT CAST(@Seconds/3600 AS VARCHAR(10))

    + RIGHT(CONVERT(CHAR(8),DATEADD(ss,@Seconds,0),108),6);

    Result:

    ----------------

    277:46:39

    (1 row(s) affected)

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • dwain.c - Thursday, March 15, 2012 5:11 AM

    Assuming you mean time as something like HH:MM:SS, this may work:DECLARE @seconds INTSELECT @seconds = 500SELECT RIGHT('00'+CAST(@seconds/3600 AS VARCHAR), 2) + ':' + RIGHT('00'+CAST(@seconds/60 AS VARCHAR), 2) + ':' +RIGHT('00''00'+CAST(@seconds % 60 AS VARCHAR) , 2) AS [HH:MM:SS]

    Will not work for negative dates and the datetime difference more than two digits in hour section

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply