Converting minutes to hh:mm:ss

  • I'm trying to develop a formula to convert minutes (my raw data) to hh:mm:ss format. E.g.: 526.30 minutes = 31,578 seconds which should read 08 hrs. 47 minutes and 8 seconds or 08:46:08 ~ I think (?)

  • What do you want to appear for durations that are greater 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)

  • Fortunately for this application I don't need to convert any to days as none will exceed about 20 hrs.

    • This reply was modified 1 month, 3 weeks ago by  DaveBriCam.
  • First of all, I recommend NOT formatting data for storage.  It's just wrong for so many reasons.

    If the data is ultimately bound for storage in a table, then do the following:

     SELECT CONVERT(TIME,DATEADD(ms,526.30*60000,0))

    If you truly need it for display purposes, you'll find an overwhelming majority of people that will say that the best place to do such formatting is in the GUI or reporting app that you're using and I totally agree with that.

    Sometimes, you've gotta do what you're gotta do (although you still need to follow the idea that it's almost never a good idea to store formatted data except in temporary objects).  With that, this will produce the formatted output you desire.

     SELECT CONVERT(CHAR(8),DATEADD(ms,526.30*60000,0),108) --24 hour time or duration

    In the code above, the "Date Serial Number" of "0" has been used.  "0" is the base date of "1900-01-01' .  Multiplying your number by 60,000 converts your number to milliseconds.  Adding that number of milliseconds to the base date creates an actual DATETIME datatype containing a date of 1900-01-01 with the correct time in hours, minutes, seconds, a milliseconds with a resolution of 3.3 milliseconds.  The CONVERT uses format # 108 to format that date and time in the 24 hour format of hh:mi:ss and dumps that to a CHAR(8) datatype for display purposes.

    For more information on formatting dates and times (which, again, is usually a mistake), please see the following MS Documentation.  I strongly recommend that you become very familiar with all that's covered in the documentation because it covers a great deal more than just dates and times and you can actually pull off small miracles using CONVERT in conjunction with other functions.

    https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15

    I also recommend that you become insanely familiar with the other date functions and datatypes because just about everything that people do in SQL Server uses some form of date/time reference.  I'll let you Google for those because, unless you've download the help-system (referred to as "Books Online" or just "BOL") to have such documentation available at your finger tips, it's a quintessential skill that folks like us need to become very good at.

    --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)

  • declare @Seconds int = 31578
    select @Seconds [@Seconds],
    t.Hours,
    u.Minutes,
    v.Seconds,
    CONCAT(RIGHT('0'+CONVERT(varchar,T.Hours),2),':',RIGHT('0'+CONVERT(varchar,U.Minutes),2),':',RIGHT('0'+CONVERT(varchar,V.Seconds),2)) [hh:mm:ss]
    from (values ((@Seconds)/3600)) T(Hours)
    cross apply (values (((@Seconds)%3600)/60)) U(Minutes)
    cross apply (values ((@Seconds%60))) V(Seconds)
  • SELECT CONVERT(CHAR(8), DATEADD(SECOND, 60 * 526.30, '00:00:00'), 8)


    N 56°04'39.16"
    E 12°55'05.25"

  • This sql works about this way:

    DECLARE @Seconds INT;

    SELECT @Seconds = 43052;

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

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

    I tested it

  • I guess I don't understand all the gyrations with separating parts and concatenating them back together for this task in SQL Server.  Is there a reason for it?  If not, please see my previous post above.

     

    --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)

  • SwePeso wrote:

    SELECT CONVERT(CHAR(8), DATEADD(SECOND, 60 * 526.30, '00:00:00'), 8)

    That's another good way (IMHO).

    --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)

  • DaveBriCam wrote:

    I'm trying to develop a formula to convert minutes (my raw data) to hh:mm:ss format. E.g.: 526.30 minutes = 31,578 seconds which should read 08 hrs. 47 minutes and 8 seconds or 08:46:08 ~ I think (?)

    I must ask why you are not storing the data in the lowest granularity, normally seconds?

    😎

    SQL Server lags behind when it comes to some temporal concepts such as duration, suggest you work around those shortcomings by using numerical data types and not using any of the existing temporal data types.

  • SwePeso wrote:

    SELECT CONVERT(CHAR(8), DATEADD(SECOND, 60 * 526.30, '00:00:00'), 8)

    I prefer this method too, with the minor change that the column name should be used rather than a hard-coded value:

    SELECT CONVERT(CHAR(8), DATEADD(SECOND, 60 * column_name, '00:00:00'), 8)

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

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

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