Aggregate minutes from varchar datatype.

• Marcus Farrugia

  SSCarpal Tunnel

  Points: 4718

  More actions

  September 23, 2013 at 7:49 am

  #277470

  Hi I have a field in a table that represents the status time spent on activity per employee. The field is stored as a varchar and is in the formatt '000:00:00' I would like to aggregate the minutes, here is a small sample size of the column; however when I try to sum this field I'm receiving this error and cannot cast to a number to aggregate.

  Msg 241, Level 16, State 1, Line 27

  Conversion failed when converting date and/or time from character string.

  Warning: Null value is eliminated by an aggregate or other SET operation.

  Any suggestions would be appreciated!

  --===== If the test table already exists, drop it

  IF OBJECT_ID('TempDB..#sum_minutes') IS NOT NULL

  DROP TABLE #sum_minutes

  --===== Create the test table with

  CREATE TABLE #sum_minutes

  (

  STATUSTRACKING VARCHAR(50)

  )

  INSERT INTO #sum_minutes

  --(STATUSTRACKING)

  SELECT '00:00:01' UNION ALL

  SELECT NULL UNION ALL

  SELECT '03:20:01' UNION ALL

  SELECT '00:00:53' UNION ALL

  SELECT NULL UNION ALL

  SELECT '00:08:25' UNION ALL

  SELECT '00:12:18' UNION ALL

  SELECT '00:12:18' UNION ALL

  SELECT '76:03:43' UNION ALL

  SELECT '00:01:05' UNION ALL

  SELECT '00:20:24' UNION ALL

  SELECT '03:58:01' UNION ALL

  SELECT '00:07:47'

  SELECT SUM(DATEPART(MINUTE, STATUSTRACKING)) FROM #sum_minutes

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  September 23, 2013 at 8:12 am

  #1652352

  The problem is that you can't store a value larger than 24 hours as time. You could use a workaround but you need to be sure to manage the string lengths correctly.

  SELECT SUM( (SUBSTRING( STATUSTRACKING, 1, 2) * 3600) + (SUBSTRING( STATUSTRACKING, 4, 2) * 60) + (SUBSTRING( STATUSTRACKING, 7, 2))) / 60

  FROM #sum_minutes

  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

• Dwain Camps

  SSC Guru

  Points: 86908

  More actions

  September 23, 2013 at 6:41 pm

  #1652527

  Another way:

  -- Result in seconds

  SELECT SUM(LEFT(STATUSTRACKING, 2) * 3600 +

  DATEDIFF(second, '1900-01-01', CAST(STUFF(STATUSTRACKING, 1, 2, '00') AS DATETIME)))

  FROM #sum_minutes

  ---

  My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

  My thought question: Have you ever been told that your query runs too fast?

  My advice:
  INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
  The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

  Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
  Since random numbers are too important to be left to chance, let's generate some![/url]
  Learn to understand recursive CTEs by example.[/url]
  [url url=http://www.sqlservercentral.com/articles/St