Post reply

Converting decimals to HH:MM:SS

  • August 9, 2018 at 6:52 am

    #368999

    Hi, I have read a lot of post about this subject and as yet can't find a solution that works for me.
    I have a decimal number
    3.25

    which is 3.25 minutes

    translated to HH:MM:SS that equals 00:03:15

    My question is how in SQL server 2008 R2 can I replicate this?
    Most of the posts I have read, disregard the seconds and they are vital for this calculation.
    example is

    CONVERT(char(8),DATEADD(minute,3.25,0),114)  =  00:03:00

    and I require  00:03:15

    Any help gratefully received.

    Dave

  • August 9, 2018 at 7:16 am

    #2000998


    DECLARE @t decimal(10,2) = 3.25;
    SELECT CONVERT(char(8), DATEADD(second, @t%1 * 60, DATEADD(minute, @t, 0)), 114);

  • August 9, 2018 at 7:29 am

    #2001000

    Ken McKelvey - Thursday, August 9, 2018 7:16 AM


    DECLARE @t decimal(10,2) = 3.25;
    SELECT CONVERT(char(8), DATEADD(second, @t%1 * 60, DATEADD(minute, @t, 0)), 114);

    There is absolutely no reason to do this as two separate operations.  Instead of just converting the decimal portion to seconds, convert everything to seconds.

    Also, you should never store date/time data as characters.

    SELECT CAST(DATEADD(SECOND, 3.25 * 60, 0) AS time(0))

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • August 9, 2018 at 7:50 am

    #2001017

    drew.allen - Thursday, August 9, 2018 7:29 AM

    Ken McKelvey - Thursday, August 9, 2018 7:16 AM


    DECLARE @t decimal(10,2) = 3.25;
    SELECT CONVERT(char(8), DATEADD(second, @t%1 * 60, DATEADD(minute, @t, 0)), 114);

    There is absolutely no reason to do this as two separate operations.  Instead of just converting the decimal portion to seconds, convert everything to seconds.

    Also, you should never store date/time data as characters.

    SELECT CAST(DATEADD(SECOND, 3.25 * 60, 0) AS time(0))

    Drew

    Worked like a charm.

    Many thanks Drew.

    Dave

  • August 9, 2018 at 8:47 am

    #2001039

    david_h_edmonds - Thursday, August 9, 2018 7:50 AM

    drew.allen - Thursday, August 9, 2018 7:29 AM

    Ken McKelvey - Thursday, August 9, 2018 7:16 AM


    DECLARE @t decimal(10,2) = 3.25;
    SELECT CONVERT(char(8), DATEADD(second, @t%1 * 60, DATEADD(minute, @t, 0)), 114);

    There is absolutely no reason to do this as two separate operations.  Instead of just converting the decimal portion to seconds, convert everything to seconds.

    Also, you should never store date/time data as characters.

    SELECT CAST(DATEADD(SECOND, 3.25 * 60, 0) AS time(0))

    Drew

    Worked like a charm.

    Many thanks Drew.

    Dave

    Note that this option will only work if the value is lower than 1440 minutes. Once you get there, it will reset to zero. If that's fine, then go ahead.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • August 9, 2018 at 9:01 am

    #2001047

    Luis Cazares - Thursday, August 9, 2018 8:47 AM

    david_h_edmonds - Thursday, August 9, 2018 7:50 AM

    drew.allen - Thursday, August 9, 2018 7:29 AM

    Ken McKelvey - Thursday, August 9, 2018 7:16 AM


    DECLARE @t decimal(10,2) = 3.25;
    SELECT CONVERT(char(8), DATEADD(second, @t%1 * 60, DATEADD(minute, @t, 0)), 114);

    There is absolutely no reason to do this as two separate operations.  Instead of just converting the decimal portion to seconds, convert everything to seconds.

    Also, you should never store date/time data as characters.

    SELECT CAST(DATEADD(SECOND, 3.25 * 60, 0) AS time(0))

    Drew

    Worked like a charm.

    Many thanks Drew.

    Dave

    Note that this option will only work if the value is lower than 1440 minutes. Once you get there, it will reset to zero. If that's fine, then go ahead.

    It's to do with call answering times, if a call is ringing longer than 1440 minutes, we have bigger problems!!! 🙂
    Thanks for the heads up though.

    Dave

  • August 11, 2018 at 6:00 pm

    #2001388

    david_h_edmonds - Thursday, August 9, 2018 9:01 AM

    Luis Cazares - Thursday, August 9, 2018 8:47 AM

    david_h_edmonds - Thursday, August 9, 2018 7:50 AM

    drew.allen - Thursday, August 9, 2018 7:29 AM

    Ken McKelvey - Thursday, August 9, 2018 7:16 AM


    DECLARE @t decimal(10,2) = 3.25;
    SELECT CONVERT(char(8), DATEADD(second, @t%1 * 60, DATEADD(minute, @t, 0)), 114);

    There is absolutely no reason to do this as two separate operations.  Instead of just converting the decimal portion to seconds, convert everything to seconds.

    Also, you should never store date/time data as characters.

    SELECT CAST(DATEADD(SECOND, 3.25 * 60, 0) AS time(0))

    Drew

    Worked like a charm.

    Many thanks Drew.

    Dave

    Note that this option will only work if the value is lower than 1440 minutes. Once you get there, it will reset to zero. If that's fine, then go ahead.

    It's to do with call answering times, if a call is ringing longer than 1440 minutes, we have bigger problems!!! 🙂
    Thanks for the heads up though.

    Dave

    If that's the case, then why isn't 3.25 minutes acceptable?  I'd also think that you'd want to add up the number of minutes per hour or per day, as well, and already having minutes in decimal form is a blessing in disguise.

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

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

  • August 12, 2018 at 12:12 am

    #2001393

    Hi, thanks for the reply, I have them in both formats, decimal and time. I am trying to cater for end users who aren’t really sure what they want.

    Kind Regards

    Dave

  • August 12, 2018 at 12:46 am

    #2001394

    Quick question, as there is no duration data type in SQL Server, why not simply record the start and end date times and when needed, simply calculate the difference to the granularity needed? 
    😎

    There are many pitfalls when emulating duration with the date and time data types and although this may seem trivial at the first glance, it is an accident waiting to happen.

  • August 12, 2018 at 6:06 am

    #2001397

    If you can collect duration in minutes, storing it as a decimal count of minutes and only ever converting it as a presentation layer thing is probably the best approach. If you do store a start and end time, consider DateTimeOffset for both ends less you end up with negative durations when leap years occur.

  • August 12, 2018 at 9:47 am

    #2001403

    All valid points for consideration however in this instance, the LOBS calculates the duration prior to the extract so I am absolved of responsibility for how it is calculated.

    Cheers

    Dave

  • August 12, 2018 at 9:55 am

    #2001404

    andycadley - Sunday, August 12, 2018 6:06 AM

    If you can collect duration in minutes, storing it as a decimal count of minutes and only ever converting it as a presentation layer thing is probably the best approach. If you do store a start and end time, consider DateTimeOffset for both ends less you end up with negative durations when leap years occur.

    That is simply an assumption and as the requirements may change, what if the business decides on billing by the millisecond? 
    😎

    In this situation, the business has to decide on the highest/lowest granularity and that defines the data collection and processes.

  • August 12, 2018 at 3:02 pm

    #2001409

    david_h_edmonds - Sunday, August 12, 2018 9:47 AM

    All valid points for consideration however in this instance, the LOBS calculates the duration prior to the extract so I am absolved of responsibility for how it is calculated.CheersDave

    Heh... True enough.  I forgot that this is probably coming directly from a phone system.  Avaya systems store call duration as whole seconds rather than decimal minutes.  At least those conversions are fairly direct.

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

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

  • August 12, 2018 at 3:05 pm

    #2001410

    Eirikur Eiriksson - Sunday, August 12, 2018 9:55 AM

    andycadley - Sunday, August 12, 2018 6:06 AM

    If you can collect duration in minutes, storing it as a decimal count of minutes and only ever converting it as a presentation layer thing is probably the best approach. If you do store a start and end time, consider DateTimeOffset for both ends less you end up with negative durations when leap years occur.

    That is simply an assumption and as the requirements may change, what if the business decides on billing by the millisecond? 
    😎

    In this situation, the business has to decide on the highest/lowest granularity and that defines the data collection and processes.

    For phone systems, I don't know of a one that stores duration of the call as anything less than whole seconds.  The same goes with call Start and End times.  Haven't seen one yet that stores such information with a resolution of less than a second.

    That, not withstanding, I suppose it could happen someday.

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

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

  • August 12, 2018 at 3:20 pm

    #2001411

    Eirikur Eiriksson - Sunday, August 12, 2018 12:46 AM

    Quick question, as there is no duration data type in SQL Server, why not simply record the start and end date times and when needed, simply calculate the difference to the granularity needed? 
    😎

    There are many pitfalls when emulating duration with the date and time data types and although this may seem trivial at the first glance, it is an accident waiting to happen.

    I have to disagree but only on a purely semantic level.  The DATETIME datatype calculates (Duration = EndDateTime-StartDateTime, directly) and stores durations as an offset from the "0" based date just fine.  It even works with negative durations if you're trying to build a countdown.  The only problem is that there isn't a convenient built-in display conversion other than format #114 (hh:mi:ss:mmm(24h)) but that's also not a huge issue.

    Please see the following article for more information:
    Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

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

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

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

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