Changing column format. - 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)

Changing column format.

Author

Topic

silvershark
Starting Member

48 Posts

Posted - 2008-12-30 : 12:47:50

I need to change from a decimal to zero's..

IE: 50.00 = 5000

Let me know what format this would be, thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 12:53:27

you mean remove decimal points? just use


Update Table
SET Col=Col * POWER(10,LEN(SUBSTRING(Col,CHARINDEX('.',Col)+1,LEN(Col))))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-31 : 03:10:27

What is the datatype of the column?

Madhivanan

Failing to plan is Planning to fail

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-31 : 05:01:30

quote:
Originally posted by visakh16

you mean remove decimal points? just use

Update Table
SET Col=Col * POWER(10,LEN(SUBSTRING(Col,CHARINDEX('.',Col)+1,LEN(Col))))

If the column is of numeric data type then Argument numeric is invalid for argument 1 of substring function.

Jai Krishna

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-31 : 05:06:07

If it is varchar field Try This

Update tbldot
SET id=replace(id,'.','')

Jai Krishna

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 05:12:49

quote:
Originally posted by Jai Krishna

quote:
Originally posted by visakh16

you mean remove decimal points? just use

Update Table
SET Col=Col * POWER(10,LEN(SUBSTRING(Col,CHARINDEX('.',Col)+1,LEN(Col))))

If the column is of numeric data type then Argument numeric is invalid for argument 1 of substring function.

Jai Krishna

then just cast it to varchar before taking substring

silvershark
Starting Member

48 Posts

Posted - 2008-12-31 : 15:40:17

So,

UPDATE AR_CUST
SET OtdDedAmt=BAL * POWER(10,LEN(SUBSTRING(OtdDedAmt,CHARINDEX('.',OtdDedAmt)+1,LEN(OtdDedAmt))))

?

I am trying to make the OtdDedAmt column to reflect 50.00 to 5000

Thanks again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-01 : 13:33:21

quote:
Originally posted by silvershark

So,

UPDATE AR_CUST
SET OtdDedAmt=BAL * POWER(10,LEN(SUBSTRING(OtdDedAmt,CHARINDEX('.',OtdDedAmt)+1,LEN(OtdDedAmt))))

?

I am trying to make the OtdDedAmt column to reflect 50.00 to 5000

Thanks again!

will you be always having figures upto two decimal places?

silvershark
Starting Member

48 Posts

Posted - 2009-01-02 : 11:59:37

Yes, I will always have two decimal places. Either a .01 through 40,000.00 in the dollar range.

Thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 12:04:22

then just use


UPDATE AR_CUST
SET OtdDedAmt= OtdDedAmt*100

i assume OtdDedAmt is of type numeric

silvershark
Starting Member

48 Posts

Posted - 2009-01-02 : 12:10:59

visakh16, that doesnt work.

Thanks though.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-02 : 12:21:12

quote:
Originally posted by silvershark

visakh16, that doesnt work.

Thanks though.

Any error? Tell us more.

silvershark
Starting Member

48 Posts

Posted - 2009-01-02 : 13:25:43

it didn't change the values.

Its still 5880.91 for the amount as an example.

Or 9.51 as another example.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 13:28:57

whts datatype of OtdDedAmt?

silvershark
Starting Member

48 Posts

Posted - 2009-01-02 : 13:29:22

T_MONEY

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-02 : 13:38:32

quote:
Originally posted by silvershark

T_MONEY

Are you sure that they are not getting updated?
Are you sure that the values you are seeing as 5880.91 and 9.51
were not 58.8091 and .0951 before ?

silvershark
Starting Member

48 Posts

Posted - 2009-01-02 : 13:42:56

Positive... I checked column BAL to verify.

silvershark
Starting Member

48 Posts

Posted - 2009-01-02 : 17:40:53

Ok, so I changed the format to numeric and checked the values... It gets rid of the zero's as I wanted, but it does not allow me to convert the values.

Here is my error message.

Error converting data type varchar to numeric
Error converting data type varchar to numeric
[update AR_CUST
set OtdDedAmt='OtdDedAmt*100']

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-03 : 00:11:37

There should be no quotes there in your query,,
Instead of

 OtdDedAmt='OtdDedAmt*100'

make it

update AR_CUST
set OtdDedAmt=OtdDedAmt*100

To check which of the columns can not be converted to numeric, you could run something like

Select * from AR_CUST where isnumeric(OtdDedAmt)<>1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-03 : 07:06:49

quote:
Originally posted by sakets_2000

There should be no quotes there in your query,,
Instead of
OtdDedAmt='OtdDedAmt*100'
make it
update AR_CUST
set OtdDedAmt=OtdDedAmt*100

To check which of the columns can not be converted to numeric, you could run something like

Select * from AR_CUST where isnumeric(OtdDedAmt)<>1

Isnumeric is not completely reliable.
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/enhanced-isnumeric-function.aspx

silvershark
Starting Member

48 Posts

Posted - 2009-01-05 : 11:46:51

It seems as all that it is doing is changing it from 4090.20 to 4090

if 50 = 5000 then I would assume that 4090 would be 40009020?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources