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 123»»»

Error converting data type nvarchar to float Expand / Collapse
Author
Message
Posted Monday, May 18, 2009 7:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 5:40 PM
Points: 60, Visits: 230
Hi I am testing one stored procedure.
I have this Test_table :

id int
name varchar
data float

I would like to update all records by condidtion:

Update Test_table set data = '27.55' where name = 'Peter'

When I tested it and appiers error with converting nvarchar to float .

Could you help me please.
Thanks


Post #719013
Posted Monday, May 18, 2009 7:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 8:24 AM
Points: 43, Visits: 214
Hi, can you post the stored procedure code?
Post #719042
Posted Tuesday, May 19, 2009 2:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 5:40 PM
Points: 60, Visits: 230
ALTER Procedure dbo.UPdata


(
@e_Nplan char(20),
@e_Ncas char(20)
)

As

DECLARE @Nplan as char(20)
DECLARE @Ncas as float(2)

SET @Nplan = @e_Nplan
SET @Ncas = CONVERT(float(2) , @e_Ncas)

Update Cis_OP_NO SET N_Cas100ks = @Ncas where N_Kplan =@Nplan

return


Post #719592
Posted Tuesday, May 19, 2009 2:34 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,550, Visits: 2,232
Ok you need to make some var changes.

You are using a CHar(20) which has a set lenght and SQL is struggling to convert that.

Have you tried changed @e_Ncas to a Float in the Procedure declaration?
I can't see any reason why it is a char to begin with...

Thanks
Chris


----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #719596
Posted Tuesday, May 19, 2009 3:52 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
peter478 (5/19/2009)
ALTER Procedure dbo.UPdata


(
@e_Nplan char(20),
@e_Ncas char(20)
)

As

DECLARE @Nplan as char(20)
DECLARE @Ncas as float(2)

SET @Nplan = @e_Nplan
SET @Ncas = CONVERT(float(2) , @e_Ncas)

Update Cis_OP_NO SET N_Cas100ks = @Ncas where N_Kplan =@Nplan

return




To much typing.
Keep it short, do only what needs to be done.
ALTER Procedure dbo.UPdata
@e_Nplan char(20),
@e_Ncas char(20)
As

Update Cis_OP_NO
SET N_Cas100ks = @e_Nplan -- I don't see any point of passing a char(20) value from one var to another char(20) one
where N_Kplan = CONVERT(float(2) , LTRIM(RTRIM(@Nplan))) -- extra spaces in char variables may cause conversion to fail

GO

Post #719634
Posted Tuesday, May 19, 2009 8:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 5:40 PM
Points: 60, Visits: 230
Hi Chris,

The reason, why I need to use type Char is that MS access doesnt work with Type float.For that reason I use Char.My entry for @e_Ncas is a number with comma. ( e.g. 15,79)
Of course when I change it to float it works but It doesnt solve my problem.
I only need to know how to convert char to float under SQL.Thats all.

Thank you
Peter
Post #719928
Posted Tuesday, May 19, 2009 9:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 8:24 AM
Points: 43, Visits: 214
converting a char to a float will only work if the char string is in fact a valid number.

declare @string1 char(20), @string2 float

set @string1 = '15,76' -- THIS WILL FAIL
--set @string1 = '15.76' -- THIS WILL WORK

print @string1

set @string2 = convert(float,@string1)

print @string2

Post #719938
Posted Tuesday, May 19, 2009 9:02 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 5:40 PM
Points: 60, Visits: 230
Hi Sergiy,

I have just tested your code but the same error message appiers.
When I use whole number e.g. 15 there is not problem, but if I use e.g. 15,28
appiers error message.

ALTER Procedure dbo.UpdateTB

@e_Nplan char(20), /* e.g. 112e */
@e_Ncas char(20) /* e.g. 15,28 */

As

Update Cis_OP_NO
SET N_Cas100ks = CONVERT(float(2) , LTRIM(RTRIM(@e_Ncas)))
Where N_Kplan = @e_Nplan

return

Thanks

Post #719941
Posted Tuesday, May 19, 2009 9:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 5:40 PM
Points: 60, Visits: 230
Thank you John ,

now it works .... so I will have to modify my source code to substitute " , " to " . "


Post #719954
Posted Tuesday, May 19, 2009 9:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 8:24 AM
Points: 43, Visits: 214
I was just gonna ask if your numbers were always formatted like '15,28' with commas then you could use

declare @string1 char(20), @string2 float

set @string1 = '152,23'

print @string1

set @string2 = convert(float,replace(@string1,',','.'))

print @string2
Post #719961
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse