September 16, 2004 at 8:16 am
i have a problem in casting varchar to float ..
when the number is very big in the varchar variable, the transered number is rounded !!
here is an example in transfering cheque number
declare
@Check nvarchar (25) , @CheckinCheques float
set @Check= '9999999999999999999999998'
set @CheckinCheques =convert(float,@Check)
print @CheckinCheques
the output is
1e+025 = = 10000000000000000000000000
also i used cast instead of convert
set @CheckinCheques =cast(@Check as float)
but it gives me the same output
how can I solve this problem??
Many thanks to all who respond,
Alamir Mohamed
Alamir_mohamed@yahoo.com
September 16, 2004 at 8:54 am
Float and Real are for approximate values only.
How realistic is your '9999999999999999999999998' figure?
September 17, 2004 at 6:28 am
You could try using the DECIMAL datatype instead, although the internal storage requirements are much higher. Here's an example that maintains 4 decimal places. You can, of course, change that as needed.
DECLARE @Check nvarchar(25), @CheckinCheques float, @CDec Decimal(38,4)
SET @Check = '9999999999999999999999998'
SET @CheckinCheques = Convert(float, @Check)
SET @CDec = Convert(Decimal(38,4), @Check)
PRINT @CheckinCheques
PRINT @CDec
September 19, 2004 at 2:33 am
i need this accuracy because it is a cheque number ..
what about if i change the column to long integer ? .. does the result will be accurate
Alamir Mohamed
Alamir_mohamed@yahoo.com
September 20, 2004 at 1:47 am
If it is a cheque number does it have to be recorded as a physical number? Can it be kept as a sting?
Computers do integer arithmetic accurately. The golden rule is never to do equality checks between floating point numbers.
September 21, 2004 at 12:49 am
i try now to convert the column to decimal(18,0) .. but does this will be fast in inner Join (if the 2 columns are decimal) ?
Alamir Mohamed
Alamir_mohamed@yahoo.com
September 28, 2004 at 10:24 am
I think the speed depends on the storage size which is 9 bytes for decimal(18, 0) and 8 bytes for floa. I think comparing decimals will be somewhat slower (more than just (9 - 8) / 8 * 100% since 8 bytes is a 32 bits exact multiple, and 9 bytes would probably be compared as three times 32 bits, but this is just an opinion).
But storing those big numbers as decimal is still your best bet, since comparing floats is troublesome and is an approximate number (see http://www.xidak.com/mainsail/other_documentation/floatingpoint.html), . And storing as varchar or worse nvarchar, will require 18 or 36 bytes.
So i recommend you go ahead and use the decimal data type for this job.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply