saving values - SQL Server Forums

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

saving values

Author

Topic

stumbling
Posting Yak Master

104 Posts

Posted - 2007-08-10 : 10:27:46

Hi All
2 post in a row first time in ages so i must be getting better but i get stuck on crappy little things like this hopefully
I have data in the following format
4.1399999999999997
4.2400000000000002
5.4800000000000004
5.1799999999999997
6.7699999999999996
i want to select the data as such
4.13
4.24
5.48
5.17
6.76
I keep chasing my tale on this one around and around any one got a simple idea i am missing, you would not believe how much time i have spent on this.
Cheers
Phil

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-10 : 10:34:32

Try this

select substring(convert(varchar(20),4.1399999999999997),1,4)

select substring(convert(varchar(20),COLUMN_NAME),1,4)

Ashley Rhodes

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-10 : 10:42:59

Or this

select FLOOR(6.7699999999999996 *100)/100

Jim

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-10 : 11:05:28

my previous solution does not work when selecting from a table and neither does floor

CREATE TABLE #Table1 (C1 float)

INSERT INTO #Table1 VALUES(4.1399999999999997)
INSERT INTO #Table1 VALUES(4.2400000000000002)
INSERT INTO #Table1 VALUES(6.7699999999999996)
INSERT INTO #Table1 VALUES(5.1799999999999997)
INSERT INTO #Table1 VALUES(5.2999999999999997)

select
cast(cast(C1 as int) as varchar(10))
+ cast(substring(cast(C1-cast(C1 as int) as varchar(10)),2,2) as varchar(4))
FROM #Table1

select floor(C1*100)/100 from #table1

This is interesting. Never paid attention to it before.

Ashley Rhodes

stumbling
Posting Yak Master

104 Posts

Posted - 2007-08-10 : 11:16:13

This is what im finding is there a way around this?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-08-10 : 11:45:56

If you are getting a rounding "error" then can we assume that you are using a FLOAT data type?

FLOAT and REAL are approximations so, I'm not sure there is anyway to get what you want. You could try casting to a NUMERIC..?

SELECT CAST(C1 AS NUMERIC(3,2))
FROM #Table1

or possibly store the data as a different datat type, like NUMERIC.

stumbling
Posting Yak Master

104 Posts

Posted - 2007-08-10 : 11:53:27

quote:
Originally posted by Lamprey

If you are getting a rounding "error" then can we assume that you are using a FLOAT data type?

FLOAT and REAL are approximations so, I'm not sure there is anyway to get what you want. You could try casting to a NUMERIC..?
SELECT CAST(C1 AS NUMERIC(3,2))
FROM #Table1

or possibly store the data as a different datat type, like NUMERIC.

Yep the retail package stores it as float i can not change this as it is out of my control. Is there a way to use substring and charindex to grab the decimal and then take everything from the start up to 2 characters pas the decimal???

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-08-10 : 12:02:50

quote:
Originally posted by stumbling

This is what im finding is there a way around this?

I doubt it. Floats are imprecise numbers. If you want to do things precisely you will need to use decimal.

DECLARE @c1 float
    ,@c2 float

SELECT @c1 = 5.2999999999999997
    ,@c2 = 5.3

SELECT @c1, @c2

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-10 : 12:13:42

I used a numeric (25,20) and this
select floor(C1*100.0)/100
produced this

4.130000
4.240000
6.760000
5.170000
5.290000

Jim

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-10 : 14:45:08

jimf can u write the query how u used numeric(25,20)

Ashley Rhodes

stumbling
Posting Yak Master

104 Posts

Posted - 2007-08-10 : 19:24:52

quote:
Originally posted by jimf

I used a numeric (25,20) and this
select floor(C1*100.0)/100
produced this

4.130000
4.240000
6.760000
5.170000
5.290000

Jim

Hi Jim can you tell me how you got this :-)Phil

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-13 : 06:46:35

I just created the table NUMERIC(25,20), but this works with float

DECLARE @Table1 TABLE (C1 float)
INSERT INTO @Table1 VALUES(4.1399999999999997)
INSERT INTO @Table1 VALUES(4.2400000000000002)
INSERT INTO @Table1 VALUES(6.7699999999999996)
INSERT INTO @Table1 VALUES(5.1799999999999997)
INSERT INTO @Table1 VALUES(5.2999999999999997)

SELECT FLOOR(CONVERT(NUMERIC(25,20),C1)*100)/100 FROM @table1

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 09:35:08

you nailed it finally
but can you explain how numeric(25,20) works and why did you select this
when

SELECT FLOOR(CONVERT(NUMERIC(38,30),C1)*100)/100 FROM @table1

SELECT FLOOR(CONVERT(NUMERIC(38,25),C1)*100)/100 FROM @table1

SELECT FLOOR(CONVERT(NUMERIC(28,25),C1)*100)/100 FROM @table1

SELECT FLOOR(CONVERT(NUMERIC(32,19),C1)*100)/100 FROM @table1

They all and many other combinations give the same result. What if I want to round to three decimal places.

Ashley Rhodes

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-13 : 09:58:44

I used Numeric (25,20) somewhat arbitrarily. There are 16 places to the right of the decimal, so I knew I needed at least that much. I'd like the SQL gods to opine on what's going on with numeric and rounding. I read a good article about rounding and data types in SQL magazine, but you need a subscription to read it on-line. I haven't found a similar article yet.

Jim

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 10:40:07

you can paste some parts of that article here if its not copyright restricted. do u need to pay for that subscription.

what is that site.

Ashley Rhodes

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-13 : 11:12:03

The link to it is here
http://www.sqlmag.com/Article/ArticleID/45921/sql_server_45921.html
you can google sql server rounding. I read the article at my last company, where I had a subscription to the print mag.

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources