issue with conversion to numeric

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

issue with conversion to numeric

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-18 : 19:04:54

Here tbl_offers have the inspect_days as numeric(length-9,precision-18,scale-0)
and the ##temp_info table have inspect_days as nvarchar..When I try to convert it was giving
me error

Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.

insert into tbl_offers
( offer_line,closed,inspect_days)

SELECT convert(datetime,Offerline),
convert(datetime,Closed),
convert(numeric(18,0),inspect_Days)as inspect_Days
FROM ##temp_info

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2004-10-18 : 22:03:06

Issue with conversion to numeric

Let me show this querry first...

declare @Aydzz as nvarchar
set @Aydzz = ''
select convert(numeric(18,0),@Aydzz) as Aydzz

When i try to run this simple querry it produces
an error just like yours. The idea of this will
show you that your inspect_Days field on your
##temp_info table has an empty value "''". Thus
converting an empty value to numeric produceses this
error...

Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.

This error happen because you are trying to convert an empty value.

How about trying this querry? I don't know if there's a built in function
that will evaluate if the expression is empty and replace it with
a specified value just like the evaluation of the function IsNULL().
So i make my own function...


CREATE FUNCTION [dbo].[LenIsZero] 
(@check_expression as varchar(50), 
 @replacement_value as varchar(50))  
RETURNS varchar(50) 
AS  
BEGIN 	
	DECLARE @ReturnValue	as varchar(50)
	IF LEN(@check_expression) = 0 
		BEGIN
			set @ReturnValue = @replacement_value
		END
	ELSE
		BEGIN
			set @ReturnValue = @check_expression
		END
RETURN (@ReturnValue)
END

----------------------------------------------------------------

Hope this new querry do some help...

insert into tbl_offers
( offer_line,closed,inspect_days)

SELECT convert(datetime,Offerline),
convert(datetime,Closed),
convert(numeric(18,0), dbo.LenIsZero(inspect_Days,NULL)) as inspect_Days
FROM ##temp_info

Want Philippines to become 1st World COuntry? Go for World War 3...

Kristen
Test

22859 Posts

Posted - 2004-10-19 : 01:29:52

Does something like this work:


insert into tbl_offers
( offer_line,closed,inspect_days)

SELECT convert(datetime,Offerline),
convert(datetime,Closed),
CASE WHEN IsNumeric(inspect_Days) = 1 THEN convert(numeric(18,0), inspect_Days)
     ELSE NULL
     END as inspect_Days
FROM ##temp_info

EDIT: Fixed syntax error
Kristen

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2004-10-20 : 02:03:46

certainly Kristen your post is better than mine.

Want Philippines to become 1st World COuntry? Go for World War 3...

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-12-02 : 08:55:02

2 things on Kristens post

1.) Kristen - shouldn't the syntax be CASE WHEN IsNumeric(inspect_Days) = 1 THEN ?
2.) Thousand spaces and currncy symbols acoompanied with a numeric value are returned true by the isnumeric function - so it isn't always fail safe.

eg:

select isnumeric('1,2345')

--Returns True:
--BUT
create table #a(col1 numeric(18, 0))
insert into #a
select case when isnumeric('1,2345') = 1 then convert(numeric(18, 0), '1,2345') else null end

The above atatement fails

Duane.

Kristen
Test

22859 Posts

Posted - 2005-12-02 : 11:14:42

1) Absolutely correct, thanks.

2) Yeah, isnumeric() is of limited use. We use a UDF and a cut-down RegEx and LIKE to do the job instead.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-03 : 02:13:13

more on Isnumeric
http://aspfaq.com/show.asp?id=2390

Madhivanan

Failing to plan is Planning to fail

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2005-12-12 : 20:00:42

Nice function Jonas do you think you can use this function in the below type of statement when you won't be aloud to use Kristen's case statement unfortunatley, can you use your custom function in a WHERE clause instead ??? ie:

SELECT l.eannumber, l.prodcode,l.[description] ,l.jerseysupplierid ,b.week31salescost
FROM sanitaUnitCost s inner join lrho1 l on
(s.EAN = l.eannumber)
inner join bell b on
(l.eannumber = b.barcode)
WHERE l.instorejersey = 'Y'
And l.JerseyUnitPrice <> convert(numeric(18,3),dbo.LenIsZero(s.UnitPrice,NULL))

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 02:00:30

What's wrong with a CASE statement there?

Personally I'd use a UDF instead ... especially as you already have in in there - why not replace LenIsZero with ConvertToInIfLenIsNotZero?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources