string to double calculation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

string to double calculation

Author

Topic

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2006-03-29 : 02:00:11

i get an error when executing ,
Select sum(ToDouble(Jan_Hours)) from employeepayments
where Employer_Number = '2346' and [Year] = '2005'

the Jan_Hours is an varchar type of column.

how to get sum(Jan_Hours) + sum(Feb_hours) +
i also need sum (sum(Jan_Hours) + sum(Feb_hours))

a_r_satish
Yak Posting Veteran

84 Posts

Posted - 2006-03-29 : 02:06:17

toDouble() in SQL??????

For getting the groupwise sum, i would prefer
Group by <fieldName> with Cube

Regards,
satish.r
"Known is a drop, Unknown is an Ocean"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-29 : 02:07:04

Does Jan_Hours have only numeric values? Then why did you use varchar datatype?
Post table structure, sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-29 : 02:09:29

is [Year] an integer column ?
If it is, you can just use [Year] = 2005

There isn't a ToDouble() function in T-SQL and there isn't a double datatype only float & real or decimal.

Is column [Jan_Hours] contain decimal places ? Why are you using varchar to store numeric data ?

you can use

select sum(convert(decimal(10,2), Jan_Hours)), sum(convert(decimal(10,2), Feb_Hours)),
       sum(convert(decimal(10,2), Jan_Hours) + convert(decimal(10,2), Feb_Hours))
from   employeepayments
where  Employer_Number = '2346' 
and    [Year] = 2005

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2006-03-29 : 02:17:34

i am importing data from an exitting DB and working and again i have to export the worked tabel. since i am having all fields in varchar , i cannot chage the table structure. so only i am not converting varchar datatype of all fields

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2006-03-29 : 02:22:43

Error ::
Select sum(convert(decimal(10,2), Jan_Hours))
from employeepayments
where Employer_Number = '2346' and [Year] = '2005'

Server: Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to numeric.

-------------
HELP ME
-------------

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-29 : 02:23:00

that make sense

EDIT : This is referring to the 02:17 post

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-29 : 02:25:50

When exporting to worked table, make sure that table has proper datatypes

Madhivanan

Failing to plan is Planning to fail

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2006-03-29 : 02:27:57

cannot understand mr.khtan. what to do ??

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-29 : 02:33:16

quote:
Originally posted by shanmugaraj

Error ::
Select sum(convert(decimal(10,2), Jan_Hours))
from employeepayments
where Employer_Number = '2346' and [Year] = '2005'

Server: Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to numeric.

-------------
HELP ME
-------------

Looks like the Jan_Hours column contains data that is not able to convert to numeric.

Try this to list out the problematic data

select top 10 Jan_Hours
from   employeepayments
where  isnumeric(Jan_Hours) = 0

and post the data here

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-29 : 02:59:19

Isnumeric is not reliable
Read this
http://aspfaq.com/show.asp?id=2390

Madhivanan

Failing to plan is Planning to fail

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2006-03-29 : 03:56:48

when i execute ...
select top 10 Jan_Hours
from employeepayments
where isnumeric(Jan_Hours) = 0

170.
170.

0
140.0
0

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-29 : 04:28:53

These looks like valid numeric data. Could there be any non-printable characters in there ?

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2006-03-29 : 04:30:11

yes.there is non-printable characters

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-29 : 04:37:16

So ... What are you going to do with this non-printable chars ?

Ignore it ? replace(Jan_Hours, char(asciicode), '')

Clean up the source file ?

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2006-03-29 : 04:47:26

no , i want to get the sum of the columns and save in other table

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-29 : 05:11:10

Are you able to find out the ascii code of these non-printable characters and remove it with replace(Jan_Hours, char(asciicode), '') before you sum() it ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-29 : 05:39:05

Did you read my first reply clearly?
Post table structure, sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources