How to convert INTEGER to TIME

  • pshvets

    SSCommitted

    Points: 1850

    Hello,

    I have a column which is integer data type and it represents number of seconds.

    I need to convert it into MINUTES:SECONDS format.

    Conversion of int into time is prohibited in SQL server.

    MOD function doesn't seem to work in my SQL Server 2008 installation either

    Could someone suggest how to do that?

    Thanks,

    Pit.

  • Michael Earl-395764

    SSC Guru

    Points: 53873

    SELECT

    CONVERT(VARCHAR,[Column] / 60) + ':' + RIGHT('00' + CONVERT(VARCHAR,[Column] % 60),2)

    FROM

    [Table]

  • gyessql

    Ten Centuries

    Points: 1097

    declare @time int

    set @time = 70

    select cast((@time / 60) as varchar(2)) + ':' + cast((@time % 60) as varchar(2))

  • pshvets

    SSCommitted

    Points: 1850

    Thanks a lot Michael!

    It works fine.

    Just curious, why MOD doesn't work in SQL 2008. Can I set some parameter to turn it on?

    Thanks,

    Pit.

  • Michael Valentine Jones

    SSC Guru

    Points: 64818

    There is no MOD function in SQL Server; % is the modulus operator

    select Mod_Test = 100 % 60, VER = left(@@version,60)

    Results:

    Mod_Test VER

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

    40 Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)

    (1 row(s) affected)

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88023

    DECLARE @seconds int;

    SET @seconds = 14400;

    SELECT DATEADD(second, @seconds, '20090101')

    ,CONVERT(char(5), DATEADD(second, @seconds, '20090101'), 108);

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • pshvets

    SSCommitted

    Points: 1850

    Michael,

    It's not I am arguing, but why SQL Server Online Help lists MOD function?

    Pit.

  • Matt Miller (4)

    SSC Guru

    Points: 124168

    pshvets (2/13/2009)


    Michael,

    It's not I am arguing, but why SQL Server Online Help lists MOD function?

    Pit.

    You sure you looking only at SQL server and not a visual basic reference as well?

    Keep in mind that the MOD operator would work in SSIS or SSRS (since in some spots they use VB syntax and operators), but not in T-SQL

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Jeff Moden

    SSC Guru

    Points: 993924

    pshvets (2/13/2009)


    Hello,

    I have a column which is integer data type and it represents number of seconds.

    I need to convert it into MINUTES:SECONDS format.

    Conversion of int into time is prohibited in SQL server.

    MOD function doesn't seem to work in my SQL Server 2008 installation either

    Could someone suggest how to do that?

    Thanks,

    Pit.

    Do you have an upper limit of 3599 seconds on this? What do you want to display if you hit 3600 seconds? What do you want to display if you hit 86400 seconds?

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Bruce W Cassidy

    SSChampion

    Points: 12481

    Jeff Moden (2/13/2009)


    Do you have an upper limit of 3599 seconds on this? What do you want to display if you hit 3600 seconds? What do you want to display if you hit 86400 seconds?

    [font="Verdana"]Those are the reasons I would recommend using the dateadd() approach (in another example above.)[/font]

  • Jeff Moden

    SSC Guru

    Points: 993924

    DateAdd or not, the op hasn't identified what happens when you exceed 59 minutes and 59 seconds. If the duration in seconds equals two days, should the answer still be expressed in minutes and seconds or not?

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • pshvets

    SSCommitted

    Points: 1850

    I don't expect my data to exceed 20-30 min. I wouldn't need any special handling for such cases.

    Thanks,

    Pit.

  • Jeff Moden

    SSC Guru

    Points: 993924

    Then, the easiest way to handle things like leading zeros and the like, is to let SQL Server do it all for you...

    SELECT RIGHT(CONVERT(CHAR(8),DATEADD(s,Seconds,0),108),5)

    FROM (--==== Your table name would go here.... this is just for demo...

    SELECT 1 AS Seconds UNION ALL

    SELECT 10 UNION ALL

    SELECT 100 UNION ALL

    SELECT 1000 UNION ALL

    SELECT 2000 UNION ALL

    SELECT 3000 UNION ALL

    SELECT 3599

    ) yourtable

    Micheal Earl's method is probably better, though... if it does go over 3559 seconds, his will show minutes over 60. Mine is probably a bit faster because it has one less CONVERT, but it will not accomodate times over 59:59 and it will not warn you.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Jeff Moden

    SSC Guru

    Points: 993924

    So... did any of this solve your problem or not?

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • pshvets

    SSCommitted

    Points: 1850

    Sorry for not posting back...

    Yes, suggestion by Michael Earl had worked for me!

    Thanks a lot for all help!

    It is very usefull portal and forum.

    Pit.

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

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