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.
| Author |
Topic |
|
taunt
Posting Yak Master
128 Posts |
 Posted - 2014-06-24 : 14:36:15
|
Hello I'm trying to convert a nvarchar column to numeric. This is what I'm doing:SELECT LoosePrice, CASE ISNUMERIC(LoosePrice) WHEN 1 THEN CONVERT(numeric(7, 2), LoosePrice) ELSE 0.00 END AS Expr1FROM MBUpdate But what keeps happening is this the field is 1031 and it converts it to 1031.00. How can I change that query so it would be 10.31?Thanks |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-06-24 : 14:44:35
|
| SELECT CAST(1031 / 100.00 AS DECIMAL(18, 2)) |
 |
|
|
taunt
Posting Yak Master
128 Posts |
Posted - 2014-06-24 : 15:24:54
|
quote:
Originally posted by Lamprey
SELECT CAST(1031 / 100.00 AS DECIMAL(18, 2))
That works on 1031 items, but how do I do it for everything? Price's go all over the place. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-06-24 : 15:30:23
|
Divide your column by 100?SELECT LoosePrice, CASE ISNUMERIC(LoosePrice) WHEN 1 THEN CONVERT(numeric(7, 2), (LoosePrice / 100.00)) ELSE 0.00 END AS Expr1FROM MBUpdate |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-06-24 : 15:31:44
|
| Also, does that a column represent money? If so, it might be better to use the proper data type. |
|
|
|
taunt
Posting Yak Master
128 Posts |
Posted - 2014-06-25 : 12:49:32
|
quote:
Originally posted by Lamprey
Also, does that a column represent money? If so, it might be better to use the proper data type.
It does, but the format is odd. It uses a lot of unneeded zero's. The field imports like this 0069700 then I cut out the extra zero's so it's 697. The field it's going to is formatted as so: numeric(7, 2), but I ran into the issue of having the decimal go over 2 spaces on every price so it would be 6.97. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-06-25 : 12:53:30
|
quote:
Originally posted by taunt
quote:
Originally posted by Lamprey
Also, does that a column represent money? If so, it might be better to use the proper data type.
It does, but the format is odd. It uses a lot of unneeded zero's. The field imports like this 0069700 then I cut out the extra zero's so it's 697. The field it's going to is formatted as so: numeric(7, 2), but I ran into the issue of having the decimal go over 2 spaces on every price so it would be 6.97.
What I meant was, instead of storing a money value in a varchar column, use the proper data type instead. That way people don't have to guess at what the column represents and you don't have to worry about bad data and converting from a varchar to decimal/money. |
|
|
|
|
|
|
|
|
Error on converting nvarchar to numiric