I've got numbers stored in my table as varchar which i need to cast to a
datatype so that i can perform an arithmetic operation on.
34.2348905444367
45.08070345435
34.6546456354354353
43.6540697929
the problem is, I need them to be cast so that i can perform calculations on
them. I need the specific value back, and not something that is rounded up
or down.
If I use float I get extra numbers appearing at the end, (ie. there is a
rounding issue)
If I try to cast as decimal(12, 16)
The scale must be less than or equal to the precision.
How can i simply get the true value of the record ?
Many thanks.decimal(28, 16)
28 = total length
16 = positions to the right of the decimal point.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Douglas Adams" wrote:
> I've got numbers stored in my table as varchar which i need to cast to a
> datatype so that i can perform an arithmetic operation on.
> 34.2348905444367
> 45.08070345435
> 34.6546456354354353
> 43.6540697929
> the problem is, I need them to be cast so that i can perform calculations
on
> them. I need the specific value back, and not something that is rounded u
p
> or down.
> If I use float I get extra numbers appearing at the end, (ie. there is a
> rounding issue)
>
> If I try to cast as decimal(12, 16)
> The scale must be less than or equal to the precision.
>
> How can i simply get the true value of the record ?
> Many thanks.
>
>|||The following works for me. But don't confuse how the number is
*displayed* with the way it is stored
CREATE TABLE T1 (x VARCHAR(20) NOT NULL PRIMARY KEY)
INSERT INTO T1 (x)
SELECT 34.2348905444367 UNION ALL
SELECT 45.08070345435 UNION ALL
SELECT 34.6546456354354353 UNION ALL
SELECT 43.6540697929 ;
SELECT CAST(x AS DECIMAL(20,16)) FROM T1
Result:
34.2348905444367000
34.6546456354354353
43.6540697929000000
45.0807034543500000
(4 row(s) affected)
David Portas
SQL Server MVP
--|||Thanks guys
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1121852998.202064.66570@.g43g2000cwa.googlegroups.com...
> The following works for me. But don't confuse how the number is
> *displayed* with the way it is stored
> CREATE TABLE T1 (x VARCHAR(20) NOT NULL PRIMARY KEY)
> INSERT INTO T1 (x)
> SELECT 34.2348905444367 UNION ALL
> SELECT 45.08070345435 UNION ALL
> SELECT 34.6546456354354353 UNION ALL
> SELECT 43.6540697929 ;
> SELECT CAST(x AS DECIMAL(20,16)) FROM T1
> Result:
> --
> 34.2348905444367000
> 34.6546456354354353
> 43.6540697929000000
> 45.0807034543500000
> (4 row(s) affected)
> --
> David Portas
> SQL Server MVP
> --
>
