September 23, 2013 at 7:49 am
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
September 23, 2013 at 8:12 am
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
September 23, 2013 at 6:41 pm
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 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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply