January 20, 2012 at 2:19 am
It's friday morning and i'm not sure if i'm going mad.
PRINT STR(98.43075,28,14) returns 98.43075000000000
PRINT STR(98.43075,28,15) returns 98.430750000000003
Can somebody please explain.
Thanks.
January 20, 2012 at 2:28 am
You added an extra decimal place and that was just enough to highlight the rounding issues associated with the Numeric data type, which is what STR assumes.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 20, 2012 at 2:49 am
Phil,
Thanks for that.
I forgot to mention that the variable is declared as DECIMAL(28,15) so I am surprised it causes a rounding issue.
I've never come across this issue before and would seem a major problem for a lot of people. I'm basically writing a script to search for numeric values across all fields where the dat type, precision and scale is the same without having to convert backwards and forwards.
I could understand if you were reducing the number of decimal places on a very long number as that would be a normal rounding issue but on a number with only 5 deciaml places I would expect when you use STR that it just fills the extra places with zero's.
I've tested this in VB.net and VBScript within SSIS and it gives the intended result so it would seem to only affect TSQL.
Why would it allow the STR function to go to a max of 16 deciaml places if it starts throwing a wobbly at 15?
Using SQL 2005 SP3 (4035).
thanks.
January 20, 2012 at 3:17 am
jasonmorris (1/20/2012)
Phil,Thanks for that.
I forgot to mention that the variable is declared as DECIMAL(28,15) so I am surprised it causes a rounding issue.
I've never come across this issue before and would seem a major problem for a lot of people. I'm basically writing a script to search for numeric values across all fields where the dat type, precision and scale is the same without having to convert backwards and forwards.
I could understand if you were reducing the number of decimal places on a very long number as that would be a normal rounding issue but on a number with only 5 deciaml places I would expect when you use STR that it just fills the extra places with zero's.
I've tested this in VB.net and VBScript within SSIS and it gives the intended result so it would seem to only affect TSQL.
Why would it allow the STR function to go to a max of 16 deciaml places if it starts throwing a wobbly at 15?
Using SQL 2005 SP3 (4035).
thanks.
My initial thoughts were that the STR function is not to blame - rather the fact that numeric rounding issues are affecting the underlying value of the variable and that STR was just truncating that at 15dp to make everything look as you would expect.
But when I tried to prove that hypothesis, I could not, so now I am puzzled along with you 🙂 Hopefully someone else will chime in with ideas.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 20, 2012 at 6:28 am
Chiming in...
http://www.sqlservercentral.com/articles/T-SQL/71565/
--Jeff Moden
Change is inevitable... Change for the better is not.
January 20, 2012 at 6:46 am
Thanks Jeff
I checked that BOL entry out this morning, then tried stuff like this:
declare @x decimal(28,15) = 98.43075
declare @y float
set @y = @x
select @x Dec, @y Float
in the expectation of being able to replicate the STR() behaviour, but could not. Any ideas?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 20, 2012 at 6:47 am
Jeff thanks for the link but I am using DECIMAL which is one of the recommendations.
So are we saying that the STR function doesn't work and therefore should be avoided?
I can't see why this is a rounding issue as there is nothing being rounded. My collegue ran the same code and it appended a 2 to the end!!
January 20, 2012 at 6:51 am
Ah, this gives the dodgy result:
select @x Dec, @y Float, cast(@y as numeric(28,15)) casted
So looks like it may be cast as float and then back to numeric in STR()
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 31, 2012 at 9:18 am
Anybody else have any ideas before I post on the MSDN forums?
January 31, 2012 at 9:35 am
I explained the behaviour of STR() (or so I thought).
For your purposes, it is not suitable. If instead you cast your variables as, say, varchar(30) and then print them, you should find that all is well.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply