Change value - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Change value

Author

Topic

pithhelmet
Posting Yak Master

183 Posts

Posted - 2007-11-01 : 09:59:39

Hi everyone

simple, but stupid question.

I have a bigint field that i need to change the
values in.

Right now, they contain values ranging from 0 to 99999

I need to change the way they are saved in the field from
0 to 00.000
99999 to 99.999

thank you for your help!!

take care
tony

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-01 : 10:04:06

how about something like...


declare @a bigint

set @a = 99999
select @a
select cast((@a/1000.0) as decimal(5,3))

pithhelmet
Posting Yak Master

183 Posts

Posted - 2007-11-01 : 10:53:22

[Microsoft][ODBC SQL Server Driver][SQL Server] Artihmetic overflow error converting numeric to data type numeric

how about that for an error message

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-01 : 10:56:58

you'll get that error if you got a bigger number than 99999.
you can either...
change the decimal to something bigger like decimal(10,3)

or not do the conversion at all i.e. select @a/1000.0

depends how bothered you are about 3 decimal places i guess?

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-01 : 11:08:32

of course, if you're actually intending to change the datatype of the column and update the field you won't need the conversion.

i.e.
alter table [urTable] alter column [urColumn] float
-- float or decimal(10,3) etc...

and then you can just...

update [urTable]
set [urColumn] = [urColumn]/1000

Em

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources