parse Real data type

Question

parse Real data type

wwei

SSC Eights!

Points: 961
More actions
March 8, 2010 at 1:19 pm

#212149

HI All,
For folloing code, I wanted to get '163773.58', but actually I got '163774'.
Can any one help me, Thanks in advance!
DECLARE @REAL1 REAL
SET @REAL1 = 163773.58
DECLARE @real REAL
SET @real = @REAL1
PRINT @real

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Seems like you've got trapped by some display issue when using PRINT...

If you'd run a SELECT instead, it's a little different- but probably still not exaclty what you're looking for. The reason for being displayed different than what you defined is caused by the REAL data type:

Straight from BOL (SQL Server Online help system):

Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.

If possible, change the data type to decimal.

DECLARE @REAL1 REAL

SET @REAL1 = 163773.58

DECLARE @real REAL

SET @real = @REAL1

DECLARE @DEC1 DECIMAL(10,3)

SET @DEC1 = @REAL1

DECLARE @DEC2 DECIMAL(10,3)

SET @DEC2 = 163773.58

SELECT @real,@real1,@DEC1,@DEC2

PRINT @real

PRINT @REAL1

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

According to BOL, both REAL and FLOAT are:

Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.

Your values are getting rounded off because of the precision required. It would work for a "lesser" value.

DECLARE @REAL1 real

SET @REAL1 = 1637.58

DECLARE @real real

SET @real = @REAL1

select @real

print @real

Alternatively, go with DECIMAL or NUMERIC data types.

DECLARE @REAL1 decimal(20,6)

SET @REAL1 = 163773.58765

DECLARE @real decimal(20,6)

SET @real = @REAL1

PRINT @real

SELECT @real

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

You beat me to it, Lutz.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

wwei SSC Eights! Points: 961 More actions · Answer 4

HI lmu92,

Thank you so much for your help.

I tried, seems still not working for me.

If I change the destination data type to either money, decimal, or number, will working for me.

Do you have any other ideas?

Thanks a lot.

wwei SSC Eights! Points: 961 More actions · Answer 5

Hi Dixie Flatline, lmu92:

Thank you for all of you!!!

LutzM SSC Guru Points: 107049 More actions · Answer 6

wwei (3/8/2010)
HI lmu92,
Thank you so much for your help.
I tried, seems still not working for me.
If I change the destination data type to either money, decimal, or number, will working for me.
Do you have any other ideas?
Thanks a lot.

Did you run the code snippet I attached previously? Or, alternatively, use the code that "The-One-Who-Frequently-Changes-His-Name-But-Can-Be-Identified-By-His-Avatar" provided. 😉

You can change it to either one of the data types you mentioned. What is it that doesn't seem to work?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 7

@bob-2 (The-One-Who...)

It's funny that both of us came up with the almost identical solution. Including the BOL quote. Especially, if you put it in perspective to the "discussion" on THE THREAD going on at the very same moment. 😀

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 8

wwei (3/8/2010)
For folloing code, I wanted to get '163773.58', but actually I got '163774'.

Use SELECT instead of PRINT as Lutz mentioned.

Use FLOAT instead of REAL here. REAL is limited to 7 digits of precision, you number has eight digits. FLOAT has 15 digits of precision.

FLOAT is also known as DOUBLE PRECISION, but no-one can be bothered to type all that.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 9

On the whole approximate data type thing...compare the following table of data type, storage size, largest number that can be stored, and precision:

REAL 4 bytes. 3.4E+38 precision = 7

FLOAT 8 bytes. 1.8E+308 precision = 15

DECIMAL (5 byte). 1E9 maximum precision = 9

DECIMAL (9 byte). 1E19 maximum precision = 19

DECIMAL (13 byte). 1E28 maximum precision = 28

DECIMAL (17 bytes). 1E38 maximum precision = 38

Precision = maximum total number of decimal digits, in all positions.

Horses verses courses.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

wwei SSC Eights! Points: 961 More actions · Answer 10

HI All,

Thank you for all of your helps and replies.

Hi Paul, your reply (REAL 4 bytes. 3.4E+38 precision = 7) helps me figure out my problems.

Again, thank you for all your help!!!

Paul White SSC Guru Points: 150467 More actions · Answer 11

No worries. 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Giri Duddu SSCommitted Points: 1638 More actions · Answer 12

Try the following

DECLARE @REAL1 float

SET @REAL1 = 163773.58

DECLARE @real float

SET @real = @REAL1

select @real

used float instead of real