Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 7,2000 » T-SQL » Error converting data type nvarchar to float
Add to briefcase
29 posts, Page 1 of 3
123»»»

Error converting data type nvarchar to float Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
peter478
Posted Monday, May 18, 2009 7:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 7:31 AM
Points: 46, Visits: 174
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
John-150025
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: Friday, February 01, 2013 9:29 AM
Points: 43, Visits: 203
Hi, can you post the stored procedure code?
Post #719042
peter478
Posted Tuesday, May 19, 2009 2:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 7:31 AM
Points: 46, Visits: 174
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
Christopher Stobbs
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,553, 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
Sergiy
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: Wednesday, May 15, 2013 5:01 PM
Points: 4,540, Visits: 8,184
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
peter478
Posted Tuesday, May 19, 2009 8:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 7:31 AM
Points: 46, Visits: 174
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
John-150025
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: Friday, February 01, 2013 9:29 AM
Points: 43, Visits: 203
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
peter478
Posted Tuesday, May 19, 2009 9:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 7:31 AM
Points: 46, Visits: 174
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
peter478
Posted Tuesday, May 19, 2009 9:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 7:31 AM
Points: 46, Visits: 174
Thank you John ,

now it works .... so I will have to modify my source code to substitute " , " to " . "
Post #719954
John-150025
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: Friday, February 01, 2013 9:29 AM
Points: 43, Visits: 203
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
29 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.