Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

CONVERT nvarchar to numeric Expand / Collapse
Author
Message
Posted Thursday, April 16, 2009 12:53 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, September 14, 2009 1:59 PM
Points: 516, Visits: 425
I need to sum up numeric data from an analysis cube however #miss is shown on the fields with zero data.

[Column 13] is the column on table #temp It was from its original table nvarchar.

I am trying to do a convert to 0.00 but am failing with each syntax I am doing.

CONVERT (decimal(2,2),[Column 13])

or

CONVERT (decimal(2,2),[Column 13],2)

Need some assistance..thanks

Post #698782
Posted Thursday, April 16, 2009 1:16 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 4, 2012 8:16 AM
Points: 121, Visits: 245
Your convert function looks fine to me...

I tried this and it worked:
DECLARE @tmp TABLE ([Column 13] nvarchar(20))
INSERT INTO @tmp SELECT '0.00'
select CONVERT (decimal(2,2),[Column 13]) from @tmp
select CONVERT (decimal(2,2),[Column 13],2) from @tmp

Have you considered using the IsNumeric() function to check whether the data is numerically convertible or not?


Rajib Bahar
http://www.rajib-bahar.com
http://www.twitter.com/rajib2k5
http://www.youtube.com/icsql
Post #698811
Posted Thursday, April 16, 2009 1:21 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, September 14, 2009 1:59 PM
Points: 516, Visits: 425
Hi Rajib,

I did the following query:

SELECT ISNUMERIC(column_13) from #temp

The column brought back 0 and 1s

Again this column that has budget data. So values like 100, 105, 105.67, 9845.43, #MISS appear

I tried to do a SELECT CONVERT(numeric 18,2),column_13) AS convert_col13 INTO #temp2 from #temp but I still get error about unable to convert nvarchar to numeric

Post #698819
Posted Thursday, April 16, 2009 1:22 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 9:04 AM
Points: 2,184, Visits: 1,976
to convert to 0.00 format you need CONVERT(decimal(3,2),[column 13]) oytherwise you will get a Arithmetic overflow error converting varchar to data type numeric. error. 0,00 is 3 digits long 2 of which are after the decimal place. decimal(2,2) means your answer is 2 digits long of which 2 digits are to the right of the decimal; example .00



Francis
Post #698821
Posted Thursday, April 16, 2009 1:48 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, September 14, 2009 1:59 PM
Points: 516, Visits: 425
Tried that and no dice

SELECT *, CONVERT(decimal(3,2),[Column 13]) AS CONVERT13
INTO #TEMP2 FROM #TEMP

Post #698854
Posted Thursday, April 16, 2009 2:14 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 9:04 AM
Points: 2,184, Visits: 1,976
If you are getting an error please post it



Francis
Post #698883
Posted Thursday, April 16, 2009 2:21 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 4, 2012 8:16 AM
Points: 121, Visits: 245
add the error, your temp table script, insert script, and anything else that gives everyone a clear picture

Rajib Bahar
http://www.rajib-bahar.com
http://www.twitter.com/rajib2k5
http://www.youtube.com/icsql
Post #698893
Posted Friday, April 17, 2009 6:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:55 PM
Points: 11,297, Visits: 13,083
jsheldon (4/16/2009)
Hi Rajib,

I did the following query:

SELECT ISNUMERIC(column_13) from #temp

The column brought back 0 and 1s

Again this column that has budget data. So values like 100, 105, 105.67, 9845.43, #MISS appear

I tried to do a SELECT CONVERT(numeric 18,2),column_13) AS convert_col13 INTO #temp2 from #temp but I still get error about unable to convert nvarchar to numeric


The bolded item in your list is at least one of the things that is causing the error. You can't convert '#MISS' to a numeric. You could try doing:

SELECT
CONVERT(numeric(18, 2), column_13) AS convert_col13
INTO
#temp2
from
#temp
WHERE -- only do ones that could be numeric.
ISNUMERIC(column_13) = 1

You could still get some errors as currency characters, '-', and '.' are among characters that cause isnumeric to return 1.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #699344
Posted Friday, April 17, 2009 7:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 9:04 AM
Points: 2,184, Visits: 1,976
try using a CASE statement like

SELECT column_13
, CASE ISNUMERIC(column_13)
WHEN 1 THEN CONVERT(numeric(18,2),column_13)
ELSE 0.00
END
FROM #temp





Francis
Post #699397
Posted Monday, April 20, 2009 8:10 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, September 14, 2009 1:59 PM
Points: 516, Visits: 425
Francis,

Your code was the best fit...thanks to all for the help...
Post #700637
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse