Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1794
  • Last Modified:

Convert varchar to decimal with scale of 5 and precision of 15

I have values in a table that are varchar but I want them to be decimal(15,5):

a1:
-0.00101962
0.00287598
-0.000636328

When I use round(cast(a1 as real),5), I get:
-1.0200000000000001E-3
2.8800000000000002E-3
-6.4000000000000005E-4


When I use cast(cast(a1 as real) as decimal(15,5)), I get:
0.00
0.00
0.00


What do I do to get:
-0.00102
-0.00288
-0.00064

from the values?

Thank you,
Rebecca
0
rss2
Asked:
rss2
  • 5
  • 4
  • 4
  • +2
1 Solution
 
rafranciscoCommented:
Try:

CAST(A1 AS DECIMAL(15,5))
0
 
nikkilockeCommented:
Hi rss2,

I just tried this on my MS SWQL Server:

create table test (decvalue varchar(20))
go
insert into test (decvalue) values('-0.00101962')
go
insert into test (decvalue) values('0.00287598')
go
insert into test (decvalue) values('-0.000636328')
go
select cast(cast(decvalue as real) as decimal(15,5)) from test

result...
-.00102
.00288
-.00064

so something odd is going wrong with yours!

[I am assuming the '-0.00288' is a typo, and it should be '0.00288']

Hope this helps,

Nikki
0
 
illCommented:
rem: it's better to do formatting on client side
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
pcsentinelCommented:
Putting

declare @a1 varchar(20)

set @a1='-0.00101962'

select cast(cast(@a1 as real) as decimal(15,5))

into my query analyzer returns

-.00102

It may be that you have not declared the varchar to be long enough, or the query you are using to return the values from the table has the same problem. Can you post the code?

regards
0
 
rss2Author Commented:
I don't know what's going on with mine..

For instance, when I do:
select cast(cast a1 as real) as decimal(15,5)) from table I still only get values that look like this:
0.00
0.00
-0.03
0.05

from values:
-0.00101962
0.00287598
-0.0289232
0.0515462

I have no idea 1) why it's not setting the precision to the 5th place after the decimal point and 2) that it changes sign.

Any ideas? Thanks.
0
 
pcsentinelCommented:
Can you do

select a1, cast(cast a1 as real) as decimal(15,5)) from table

and post the results

regards
0
 
rss2Author Commented:
a1                                                 a1_casted        
-------------------------------------------------- -----------------
-0.00101962                                        0.00
0.00287598                                         0.00
-0.000636328                                       0.00
0                                                  0.00
-0.0289232                                         -0.03
-0.000159124                                       0.00
0.0515462                                          0.05
0                                                  0.00
-0.0014656                                         0.00
0                                                  0.00
0                                                  0.00
-0.0044736                                         0.00
-0.0128225                                         -0.01
-0.0236664                                         -0.02
0.0135267                                          0.01
-0.00620476                                        -0.01
-0.0127401                                         -0.01
-0.0407546                                         -0.04
0                                                  0.00
0.00131889                                         0.00
1.89387                                            1.89
0                                                  0.00
-0.00398554                                        0.00
0
 
rafranciscoCommented:
What happens if you don't cast it to real but straight to decimal(15,5)

select cast(a1 as decimal(15,5))

Do you get the same result?
0
 
rss2Author Commented:
"Error converting data type varchar to numeric"

.. and I think this is do to the values that have "E" in them, like this:
9.55467e-005

.. because up until this value, the values are:

-0.00101962
0.00287598
-0.000636328
0
-0.0289232
-0.000159124
0.0515462
0
-0.0014656
0
0
-0.0044736
-0.0128225
-0.0236664
0.0135267
-0.00620476
-0.0127401
-0.0407546
0
0.00131889
1.89387
0
-0.00398554
0
0.0383467
0.0030035
0
0.0386062
0.0108751
-0.346154
0
0
0
-0.00359704
-0.0247701
0
0.00510296
-0.0028075
0.0023766
-0.00792811
-0.0108852
0.0220472


.. and all seem to convert just fine.

What do you think? Thanks!

0
 
rafranciscoCommented:
Try this:

SELECT CASE WHEN CAST(A1 AS REAL) < 0.00000 THEN 0.00000 ELSE CAST(A1 AS DECIMAL(15,5)) END
0
 
rss2Author Commented:
Got "Error converting data type varchar to numeric" again. It did the first 42 values just fine, until value 43 which has the "e" in it:
9.55467e-005

0
 
pcsentinelCommented:
Ok I put the first few into a table on mine and running

select a1, cast(cast(a1 as real) as decimal(15,5)) from vtabs1

I get

-0.00101962      -.00102
0.00287598      .00288
-0.000636328      -.00064
0      .00000
-0.0289232      -.02892
-0.000159124      -.00016

But I might have the answer, I presume you are getting these results in Query Analyzer

Go into Tools > Options > Connections

have you got use regional settings when displaying.... switched on ? if you have turn it of

If I turn it on I get the following results

-0.00101962      0.00
0.00287598      0.00
-0.000636328      0.00
0      0.00
-0.0289232      -0.03
-0.000159124      0.00

regards
0
 
rafranciscoCommented:
Try this:

SELECT CASE WHEN CHARINDEX('E', A1) != 0 THEN CAST(CAST(A1 AS REAL) AS DECIMAL(15,5)) ELSE CAST(A1 AS DECIMAL(15,5)) END

But I think pcsentinel found your problem.
0
 
rss2Author Commented:
Yes! Thank you very much.

So when I convert these then they'll convert to what is shown when displaying regional settings is turned off?
0
 
pcsentinelCommented:
Yes

regards
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 4
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now