Covert Decimal to Int 1

[aumstu]

I am trying to convert decimal numbers into a number without decimals…

Sample Data

Now	Needs to Be
23.00	2300
68.67	6867
42.36	4236

All the decimals are two places.

I have used this sql statement to view the data

select table_hours, cast((table_hours * 100) as int) AS NewHours
From table

This pulls back the correct format that I want…but when I try to use the update I get the following error.

Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.

Here is my update statement

Update TABLE
SET TABLE_HOURS = cast((table_hours * 100) as int)

Does anyone know how I can update these field with the new format?

Thanks

 

[SQLDenis]

what's the scale and precision on the TABLE_HOURS field?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

run this to find out scale and precision
also what is the table name?

select numeric_precision,numeric_scale
from information_schema.columns
where column_name ='table_hours'
--and table_name ='table ' -??

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[aumstu]

Thanks sqldenis...Is the precision that the decimal place could be to?

the results are

5 2

The name of the table is SDBHOURS

thanks

 

[SQLDenis]

you have to change it from (5,2) to (5,2)
declare @d decimal(5,2)
select @d =1234 --fails
select @d
go
declare @d2 decimal(6,2)
select @d2 =1234 --ok
select @d2

why don't you make the field an int

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[aumstu]

thanks...

the math is working but whenever I the decimal places are still in the field.

The reason I havn't changed to a int is because of the decimal places. The data is imported using a dts package. I think that I will change the field to a int and then multiply by 100 in the dts package.

thanks again

 

[SQLDenis]

you see decimal places because of the 6,2 try this

declare @d decimal(5,0)
select @d =1234 --
select @d

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[williamkremer]

Denis, wouldn't that just truncate off the 2 digits to the right of the decimal? Or will it shift the decimal? And, what will happen to 12.34 if it is cast as int? This: 1234 or this: 12
You are a great teacher, and george and vongrunt and sqlsister, SQLbill and many others. You are appreciated!

"You cannot hold a torch to another man's path without brightening your own"
Best regards,
Bill

 

[williamkremer]

And you jbenson!

"You cannot hold a torch to another man's path without brightening your own"
Best regards,
Bill

 

[SQLDenis]

here is your answer
select convert(int,12.34),convert(int,(12.34*100))
OP has to do *100 of course

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[aumstu]

Thanks again for all your help