Friday, February 10, 2012

converting nvarchar to Minutes:Seconds in MS SQL?

I have a field in nvarchar type. It contains data like 0, :23, 1:57, ... all in minutes and seconds. Now, I need to convert it to MM:SS using query and get the Average of this column. How can I do it? I have tried Avg(Convert(nvarchar(20), [Calling Time], 108)) .. but I got error :The average aggregateoperation cannot take a nvarchar data type as an argument.

Help!!!! :(

You need to convert the minutes and seconds into some decimals which can be used in Avg function. Let's say you want to get Avg seconds, then you may try:

SELECT AvgSeconds= AVG(CASE WHEN CHARINDEX(':',[Calling Time])=0
THEN CONVERT(DECIMAL(4,2),[Calling Time])*60
WHEN CHARINDEX(':',[Calling Time])=1
THEN CONVERT(DECIMAL(4,2),RIGHT([Calling Time],LEN([Calling Time])-1))
ELSE CONVERT(DECIMAL(4,2),SUBSTRING([Calling Time],1,CHARINDEX(':',[Calling Time])-1))*60
+CONVERT(DECIMAL(4,2),SUBSTRING([Calling Time],CHARINDEX(':',[Calling Time])+1,LEN([Calling Time])))
END)
FROM test_CntTime

No comments:

Post a Comment