There is a select query in the existing application which I have to work, which contains the formula that deals with currency value as, SELECT CAST((CAST(RecordCount as FLOAT(20) * CAST(Sum(Power(Amount,2)) as FLOAT(20)) - Power(Sum(Amount),2)) / CAST((RecordCount * (RecordCount- 1)) as FLOAT(20)) Consider below are the values for this expression, SELECT CAST((57 * CAST(8056893945.093 as FLOAT(20)) - 207067699401.4860) / (57 * (57- 1)) as FLOAT(20)) But in the above expression the CAST(8056893945.093 as FLOAT(20)) is giving the result as 8.056894E9 which is equivalent to 8056894000 right. Because of this truncation or rounding, the final value I am getting is 79002280 (If I calculate manually the exact result by zero decimal place is 79002273). Could any one please explain why the truncation or rounding off happens (My assumption here is value out of range) and how the rounding off is carried out in SQL Server in this type of case? Thanks In Advance[]
Hi, Having a problem using an SQL query i don't know how to count the number of stored procedures inside a database using an sql query? Any help would be appreciated?
[quote user="Amarlai"] Hi, Having a problem using an SQL query i don't know how to count the number of stored procedures inside a database using an sql query? Any help would be appreciated? [/quote] Please don't hijack other threads. Open a new thread if the problem still exists.
When you use FLOAT(20) for casting the precision would be 7 digit only thats why you see the truncation/rounding. Don't use any number any use only FLOAT which will take the default value as 53 and you will not see any truncation. For more information, please look into: http://msdn.microsoft.com/en-us/library/ms173773.aspx
Yes even I found the same reason. But this has been used in theexisting application, so before changing that I have clarified thiswith experts. Thanks for your help[]