How to Take Sum(varchar) values in SQL Server

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi Friends

I need to take Sum(some varchar values) from my Table Its var char thats why I tried to cast that values first like this

(Select SUM((convert(int,RunTime))) from #TempReport)

or

SELECT SUM(CAST(RunTime AS int)) from #TempReport

Then also its showing some conversion errors : Problem is that my varchar field contain some values like 2:30,5:20,6:10 like this so how to remove the ':' this symbol and take sum?

Please give me some ideas...

Posted 29-Mar-12 23:25pm

Tony Tom.k

Add a Solution

Comments

Saral S Stalin 30-Mar-12 5:28am

You want to change 2:30 to 230 or 2, 30?

Tony Tom.k 30-Mar-12 5:35am

I need result in 0:00 format

Saral S Stalin 30-Mar-12 5:36am

Did not get that Bro. What would be the expected sum of 2:30, 5:20, 6:10?

Tony Tom.k 30-Mar-12 5:38am

I need to generate some report in excel So the Sum look like this 13:60

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Saral S Stalin · Accepted Answer · 2012-03-29T23:53:00

Solution 1

Here you go

SQL

DECLARE @TempReport TABLE (RunTIMe VARCHAR(20))
INSERT @TempReport VALUES ('2:30')
INSERT @TempReport VALUES ('5:20')
INSERT @TempReport VALUES ('6:10')

SELECT  SUM(CAST(REPLACE(RunTime, ':','.') AS float)) from @TempReport

If '.' has to replaced to ':' cast to varchar again and do a replace

Posted 29-Mar-12 23:53pm

Saral S Stalin

Comments

Tony Tom.k 30-Mar-12 5:59am

Thank You Bro Its Working Fine

Saral S Stalin 30-Mar-12 6:01am

Happy to help..If it answeres your question mark it as answer

member60 30-Mar-12 6:06am

My 5!