Thursday, March 22, 2012

Change decimal point

How can I change decimal point using SQL command ?
hi Ricardo,
Ricardo Luiz wrote:
> How can I change decimal point using SQL command ?
if you mean you want to alter a column in order to add additional
"precision" to a decimal type column, you can perform an ALTER TABLE ALTER
COLUMN statement like
SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE dbo.test (
ID int NOT NULL PRIMARY KEY ,
d decimal (18,2) NOT NULL DEFAULT 0
);
INSERT INTO dbo.test VALUES ( 1 , 1.12 );
GO
PRINT 'fails inserting all decimals'
INSERT INTO dbo.test VALUES ( 2 , 1.123 );
SELECT * FROM dbo.test;
GO
ALTER TABLE dbo.test
ALTER COLUMN d decimal (18,4);
INSERT INTO dbo.test VALUES ( 3 , 1.123 );
SELECT * FROM dbo.test;
GO
DROP TABLE dbo.test;
if you like to modify the decimal separator, than you can not, as this
setting is by design and not dependent to the language associated to the
current login as for date formatting..
you can however cast the value to a varchar(n) and replace the separator
with your own costant, but this way you no longer have a decimal type result
but a varchar..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

No comments:

Post a Comment