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

Question

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

satnam.singh 44358

Ten Centuries

Points: 1305
More actions
June 9, 2025 at 6:06 pm

#4605043

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

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

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

Mr. Brian Gale SSC-Insane Points: 24968 More actions · Answer 1

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.

Grant Fritchey SSC Guru Points: 399289 More actions · Answer 2

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

Mr. Brian Gale SSC-Insane Points: 24968 More actions · Answer 3

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.

DesNorton SSC-Insane Points: 24571 More actions · Answer 4

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

How to post data/code on a forum to get the best help.

Jo Pattyn SSC-Dedicated Points: 32513 More actions · Answer 5

Grant probabily wrote it that way for readability

with solution as (....) /*sampledata and solution*/Select ... from solution /* verify */

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 6

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)