How do I add all the values in the column considering it to be in hh:mm:ss:ms

  • How do I add all the values in the column considering it to be in hh:mm:ss:ms

    Please help.

     

  • My first thought is "why?"... but if that is your odd requirement, and I am understanding your request correctly (INSERT all possible values in hh:mm:ss:ms format), the easiest way I can think of is with a loop. Have a while loop that counts from 0 to 23 for the hours, 0 to 60 for the minutes, 0 to 60 for the seconds and 0 to 999 for the ms, and nest those loops.

    Now, if that's not what you meant, I think you will need to clarify a bit.

    I also do not see the benefit to doing this and can't think of a scenario where I'd want to do it, but if it was required for some odd reason, that's how I'd do it.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • Something like this would do it:

    WITH datebreakdown
    AS (SELECT DATEPART(hh, sod.ModifiedDate) AS hourval,
    DATEPART(mi, sod.ModifiedDate) AS minval,
    DATEPART(ss, sod.ModifiedDate) AS secval,
    DATEPART(ms, sod.ModifiedDate) AS msval
    FROM Sales.SalesOrderDetail AS sod)
    SELECT (db.hourval + db.minval + db.secval + db.msval) AS linetotal
    FROM datebreakdown AS db;

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Possibly a silly question Grant, but could you do that without the CTE? What I mean is something like:

    SELECT (DATEPART(hh, sod.ModifiedDate) + 
    DATEPART(mi, sod.ModifiedDate) +
    DATEPART(ss, sod.ModifiedDate) +
    DATEPART(ms), sod.ModifiedDate)) AS LineTotal
    FROM Sales.SalesOrderDetail AS sod

    Or does the CTE offer a performance benefit or does the above not even work (I didn't try it... just can't think why it wouldn't work).

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • It's a bit long-winded, but this is what I came up with

    DECLARE @TestData table (id int, dateVal varchar(12));

    INSERT INTO @TestData ( id, dateVal )
    VALUES ( 1, '00:02:03:123' )
    , ( 1, '01:00:13:456' )
    , ( 2, '12:03:03:782' )
    , ( 2, '15:04:09:931' )
    , ( 3, '00:00:01:666' );

    WITH cteSpltData AS (
    SELECT td.id
    , splitVal.hh
    , splitVal.mm
    , splitVal.ss
    , splitVal.ms
    FROM @TestData AS td
    CROSS APPLY (VALUES (REPLACE(td.dateVal, ':', '.'))) AS fmt(dt)
    CROSS APPLY (VALUES ( CAST(PARSENAME(fmt.dt, 4) AS int)
    , CAST(PARSENAME(fmt.dt, 3) AS int)
    , CAST(PARSENAME(fmt.dt, 2) AS int)
    , CAST(PARSENAME(fmt.dt, 1) AS int)
    )) AS splitVal(hh, mm, ss, ms)
    )
    , cteAgg AS (
    SELECT cte.id
    --, totHrs = SUM(cte.hh)
    --, totMins = SUM(cte.mm)
    --, totSec = SUM(cte.ss)
    --, totMili = SUM(cte.ms)
    , TotalTime = DATEADD(HOUR, SUM(cte.hh), DATEADD(MINUTE, SUM(cte.mm), DATEADD(SECOND, SUM(cte.ss), DATEADD(MILLISECOND, SUM(cte.ms), 0))))
    FROM cteSpltData AS cte
    GROUP BY cte.id
    )
    SELECT agg.id
    , TotalTime = /* The HOURS */ CONVERT(varchar(10), 24 * DATEDIFF(dd, 0, agg.TotalTime) + DATEPART(HOUR, agg.TotalTime))
    + /* The Rest */ SUBSTRING(CONVERT(varchar(12), CAST(agg.TotalTime AS time(3)), 114), 3, 12)
    FROM cteAgg AS agg

  • Grant probabily wrote it that way for readability

     

    with solution as (....) /*sampledata and solution*/Select ... from solution /* verify */
  • satnam.singh 44358 wrote:

    How do I add all the values in the column considering it to be in hh:mm:ss:ms

    Please help.

    WHAT is the datatype of the column?  If it's character based, what is the resolution of the "ms" part and does it have a "hidden" resolution like 3.3333 milliseconds?  If it's not character based, what is the datatype being used.

    The reason why I ask is for "performance reasons".  Character-based data for times is not only a PITA but the use of multiple DATEADDs can really slow things.  The use of "0" for the base-date will also automatically use DATETIME and that can have some unintended consequences if the original times weren't of the DATETIME datatype with the 3.3333 millisecond rounding being the issue.

    --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 7 posts - 1 through 7 (of 7 total)

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