Choosing Floating Point DataTypes in Sql? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Choosing Floating Point DataTypes in Sql?

Author

Topic

ZarrinPour
Yak Posting Veteran

66 Posts

Posted - 2008-04-28 : 15:18:57

Hi all
I'm a newbie in SQL server and please excuseme for this silly question, Could anyone tell me when i should use which of the following types:

Decimal
Float
Real

I've mixed up !!! all of them can have floating point BUT what's the difference? some advise please!

Thanks in advance.
Kind Regards.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 15:20:31

http://msdn2.microsoft.com/en-us/library/ms187912.aspx

ZarrinPour
Yak Posting Veteran

66 Posts

Posted - 2008-04-28 : 16:25:50

quote:
Originally posted by visakh16

http://msdn2.microsoft.com/en-us/library/ms187912.aspx

Hi visakh16
Thanks 4 ur reply
According to this page :
Approximate numeric data types do not store
the exact values specified for many numbers;
they store an extremely close approximation of the value

Could you please give me a T-sql code that illustrate the above BOLD
sentence in action just by an example? (you know yesterday my friend asked this question from me and a i couldn't satisfy him i need an example!!!)

I appreciate your Help.
Kind Regards.

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2008-04-29 : 06:14:46

If you try to store a currency value like $ 50.45 You do not want to use a float or a real but a Numeric (18,2) or a decimal (18,4) or something like that.

If you take 50% of 50.45 you'll get 25.222222222 or something like that if using a float or a real. If you'd use a decimal(18,4) you'll get 25,225

So if you are dealing with numbers for money, hours, or quantities, do not use float or real.

Hope this helps.

Henri
~~~~
There's no place like 127.0.0.1

ZarrinPour
Yak Posting Veteran

66 Posts

Posted - 2008-04-29 : 15:19:45

quote:
Originally posted by henrikop

If you take 50% of 50.45 you'll get 25.222222222 or something like that if using a float or a real. If you'd use a decimal(18,4) you'll get 25,225

Hi henrikop
Thanks 4 ur reply

i ran yor example (50.45/2) But sql returned 25,225 !!!
you know it is about several years that i'm using float as my data type in my Accounting system and so far i haven't had any problem
so i need a real example

Anyway thanks so much for following my case.
Kind Regards.

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2008-04-29 : 15:37:20


declare @v1 float
declare @v2 float
declare @v3 float

SELECT @v1 = 40.45, @v2 =.50
SELECT @v3 = @v1 * @v2


select @v3 / 2.3, CAST( (@v3 /2.3) As real), CAST( (@v3 /2.3) As decimal (9,2))

Returns

---------------------- ------------- ------------------------------
8,79347826086957 8,793478 8,79

Hmm, it's not a good example. However, if you do something with accounting you want to use NUMERIC en DECIMAL and not REAL or FLOAT.

Henri
~~~~
There's no place like 127.0.0.1

ZarrinPour
Yak Posting Veteran

66 Posts

Posted - 2008-04-29 : 15:45:48

henrikop, I lack the words with which to express my thanks.
Regards.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources