Re: [sqlite] floor help (plus bug found)
Hi,
At 00:11 13/12/2009, you wrote:
Sir any ida how can value rounddown floor have done
if not possible i have make small code
i requard make function please say how can add
i send you my rounddown funtion
please
Cose Exmaple :
value=10.666
decimal=1
Create roundd{value,decimal){
if (decimal0)
d=help requaird on decimal(10**)
Select Cast(value As integer)||substr((value-Cast(value As
integer))*10*d,1,decimal)*(1/d) As rounddown;
return rounddown;
}
i am c# devloper so i know padright please make for me complete
function for rounddown my arjent requairment
how add on SQLite please say
thanks advance
I am forwarding your mail to the list as well because readers may be
able to help you on the C# part.
If your question is about rounding values at the SQLite level at some
fixed number of decimal place, here is an example using the sqlite3
program.
Say we have a table created:
CREATE TABLE Samples (myValue FLOAT);
Now let us insert some values both positive and negative:
INSERT INTO Samples VALUES(6206.460984);
INSERT INTO Samples VALUES(259.026716);
INSERT INTO Samples VALUES(652864.9244028);
INSERT INTO Samples VALUES(5866.5317364);
INSERT INTO Samples VALUES(13.4058616);
INSERT INTO Samples VALUES(0.444);
INSERT INTO Samples VALUES(0.);
INSERT INTO Samples VALUES(1.0);
INSERT INTO Samples VALUES(-6206.460984);
INSERT INTO Samples VALUES(-259.026716);
INSERT INTO Samples VALUES(-652864.9244028);
INSERT INTO Samples VALUES(-5866.5317364);
INSERT INTO Samples VALUES(-13.4058616);
INSERT INTO Samples VALUES(-0.444);
INSERT INTO Samples VALUES(-0.);
INSERT INTO Samples VALUES(-1.0);
Now execute a simple query to show you how the round() SQLite function
works:
select myValue, round(myValue, 3), round(myValue) from Samples;
6206.460984 6206.4616206.0
259.026716 259.027 259.0
652864.9244028 652864.924 652865.0
5866.53173645866.5325867.0
13.4058616 13.406 13.0
0.444 0.444 0.0
0. 0.889 1.0
1.0 1.0 1.0
-6206.460984-6206.461 -6206.0
-259.026716 -259.027-259.0
-652864.9244028 -652864.924 -652865.0
-5866.5317364 -5866.532 -5867.0
-13.4058616 -13.406 -13.0
-0.444 -0.444 0.0
-0. -0.889 -1.0
-1.0-1.0-1.0
You can see that SQLite round(myValue, 3) rounds to the 3rd decimal
place and round(myValue) or round(myValue, 0) rounds down to integer (0
decimal places).
But if you look more closely, you can also notice that the rounding
direction is not correct for positive values with a fractional part
when a non-null second parameter is given. I did not notice that point
in my first reply because I only rounded to integers.
I believe there is a bug here.
Take for instance the first value above: 6206.460984
It is correctly rounded _down_ to the integer value 6206
it is wrongly rounded _up_ to the 3rd decimal place 6206.461
but the correct rounding down should be 6206.460
Rounding down negative values (integral or not) works well.
Thus the correct rounding down at 3rd decimal places using SQLite can
be done so:
case
when myValue 0 and cast(myValue as text) round(myValue) then
round(myValue - 0.00051, 3)
else
round(myValue, 3)
end
Let us see if that does what we want:
select myValue, case when myValue 0 and cast(myValue as text)
round(myValue) then round(myValue - 0.00051, 3) else round(myValue, 3)
end as Correct rounding from Samples;
6206.460984 6206.46
259.026716 259.026
652864.9244028 652864.924
5866.53173645866.531
13.4058616 13.405
0.444 0.444
0. 0.888
1 1.0
-6206.460984-6206.461
-259.026716 -259.027
-652864.9244028 -652864.924
-5866.5317364 -5866.532
-13.4058616 -13.406
-0.444 -0.444
-0. -0.889
-1 -1.0
The output is now correct in every case (I hope so) but it is finally
much less practical than we would like! Working with floating point
can bring unexpected problems.
Now if you need to write a similar function to perform the same
operation in C# then you should search MSDN C#. It is possible that
someone here with some C# knowledge could help you.
Anyway may I strongly suggest you read tutorials or books about your
language and also read the SQLite tutorial. Also you would certainly
benefit from reading about the virtues and the dark sides of
floating-point. Finally such rounding could be better done at some
point in the applicative code: it all depends on your application.
I hope this helps.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] floor help (plus bug found)
[ I apologize if this appears twice on the list ]
Hi,
At 00:11 13/12/2009, you wrote:
Sir any ida how can value rounddown floor have done
if not possible i have make small code
i requard make function please say how can add
i send you my rounddown funtion
please
Cose Exmaple :
value=10.666
decimal=1
Create roundd{value,decimal){
if (decimal0)
d=help requaird on decimal(10**)
Select Cast(value As integer)||substr((value-Cast(value As
integer))*10*d,1,decimal)*(1/d) As rounddown;
return rounddown;
}
i am c# devloper so i know padright please make for me complete
function for rounddown my arjent requairment
how add on SQLite please say
thanks advance
I am forwarding your mail to the list as well because readers may be
able to help you on the C# part, and for another good reason.
If your question is about rounding values at the SQLite level at some
fixed number of decimal place, here is an example using the sqlite3
program.
Say we have a table created:
CREATE TABLE Samples (myValue FLOAT);
Now let us insert some values both positive and negative:
INSERT INTO Samples VALUES(6206.460984);
INSERT INTO Samples VALUES(259.026716);
INSERT INTO Samples VALUES(652864.9244028);
INSERT INTO Samples VALUES(5866.5317364);
INSERT INTO Samples VALUES(13.4058616);
INSERT INTO Samples VALUES(0.444);
INSERT INTO Samples VALUES(0.);
INSERT INTO Samples VALUES(1.0);
INSERT INTO Samples VALUES(-6206.460984);
INSERT INTO Samples VALUES(-259.026716);
INSERT INTO Samples VALUES(-652864.9244028);
INSERT INTO Samples VALUES(-5866.5317364);
INSERT INTO Samples VALUES(-13.4058616);
INSERT INTO Samples VALUES(-0.444);
INSERT INTO Samples VALUES(-0.);
INSERT INTO Samples VALUES(-1.0);
Now execute a simple query to show you how the round() SQLite function
works:
select myValue, round(myValue, 3), round(myValue) from Samples;
6206.460984 6206.4616206.0
259.026716 259.027 259.0
652864.9244028 652864.924 652865.0
5866.53173645866.5325867.0
13.4058616 13.406 13.0
0.444 0.444 0.0
0. 0.889 1.0
1.0 1.0 1.0
-6206.460984-6206.461 -6206.0
-259.026716 -259.027-259.0
-652864.9244028 -652864.924 -652865.0
-5866.5317364 -5866.532 -5867.0
-13.4058616 -13.406 -13.0
-0.444 -0.444 0.0
-0. -0.889 -1.0
-1.0-1.0-1.0
You can see that SQLite round(myValue, 3) rounds to the 3rd decimal
place and round(myValue) or round(myValue, 0) rounds down to integer (0
decimal places).
But if you look more closely, you can also notice that the rounding
direction is not correct for positive values with a fractional part
when a non-null second parameter is given. I did not notice that point
in my first reply because I only rounded to integers.
I believe there is a bug here.
Take for instance the first value above: 6206.460984
It is correctly rounded _down_ to the integer value 6206
it is wrongly rounded _up_ to the 3rd decimal place 6206.461
but the correct rounding down should be 6206.460
Rounding down negative values (integral or not) works well.
Thus the correct rounding down at 3rd decimal places using SQLite can
be done so:
case
when myValue 0 and cast(myValue as text) round(myValue) then
round(myValue - 0.00051, 3)
else
round(myValue, 3)
end
Let us see if that does what we want:
select myValue, case when myValue 0 and cast(myValue as text)
round(myValue) then round(myValue - 0.00051, 3) else round(myValue, 3)
end as Correct rounding from Samples;
6206.460984 6206.46
259.026716 259.026
652864.9244028 652864.924
5866.53173645866.531
13.4058616 13.405
0.444 0.444
0. 0.888
1 1.0
-6206.460984-6206.461
-259.026716 -259.027
-652864.9244028 -652864.924
-5866.5317364 -5866.532
-13.4058616 -13.406
-0.444 -0.444
-0. -0.889
-1 -1.0
The output is now correct in every case (I hope so) but it is finally
much less practical than we would like! Working with floating point
can bring unexpected problems.
Now if you need to write a similar function to perform the same
operation in C# then you should search MSDN C#. It is possible that
someone here with some C# knowledge could help you.
Anyway may I strongly suggest you read tutorials or books about your
language and also read the SQLite tutorial. Also you would certainly
benefit from reading about the virtues and the dark sides of
floating-point. Finally such rounding could be better done at some
point in the applicative code: it all depends on your application.
I hope this helps.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
