FTS Index Size: decimal

显示标签为“decimal”的博文。显示所有博文

2012年3月29日星期四

floor function

I am trying to pull the number preceeding the decimal, but I want my output in a fixed lengh. Here is what I tried thinking it might work, however it did not.

sf_retail = right('000' + floor(cast(labsf.last_retail_price as varchar)),3),

the number I am running this against is '0000001.45' I would like my output to read '001'.....I am getting only '1'

Any suggestions?We just did something like that...

Check out...

http://www.dbforums.com/t987264.html|||Thanks, that was helpful. I ended up using:

sf_retail = right('000' + convert(varchar(3), floor(labsf.last_retail_price)),3),

floating point truncation

How can I truncate a floating point number to required number of decimal points
Eg:
100.642364074 to 100.64 and 67.643929847 to 67.645
Thanks.STR|||...And the last number should be rounded to 67.644 not 67.645

floating point calculation

I'm trying to perform a calculation on a field in SQL Server that
should return a value with a decimal point. My problem is that the
value returned is truncated without the decimal point. Is there a
setting that needs to be turned on in SQL server to allow this?

for example

Select 20/3

should return 6.6666667

but instead I get 6"Never" <nevermind@.iname.com> wrote in message
news:e43b4225.0405071534.9a29f0a@.posting.google.co m...
> I'm trying to perform a calculation on a field in SQL Server that
> should return a value with a decimal point. My problem is that the
> value returned is truncated without the decimal point. Is there a
> setting that needs to be turned on in SQL server to allow this?
> for example
> Select 20/3
> should return 6.6666667

No, you should get 6.

Try select 20.0/3.0

You'll get 6.666666

When you say select 20/3 you're telling SQL Server you're starting with
ints, so it converts the answer to an int.

> but instead I get 6

float vs decimal - computation time

I have heard that using floats in calculated fields are much faster than
using decimals. Is this true? What is the role of the floating-point
processor in these computations?
JasonCP Developer (steved@.newsgroup.nospam) writes:
> I have heard that using floats in calculated fields are much faster than
> using decimals. Is this true? What is the role of the floating-point
> processor in these computations?
Instead of asking again, why not researching the responses to your post
from Wednesday?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland et al,
I apologize. I meant to follow-up my earlier post and I realize that it was
both unclear and unnecessary to repost. I have found what I was looking for.
Thank you very much for your help.
CP Developer
"Erland Sommarskog" wrote:

> CP Developer (steved@.newsgroup.nospam) writes:
> Instead of asking again, why not researching the responses to your post
> from Wednesday?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>

Float vs Decimal

Hello, sorry if this question is silly
I'm

about difference between Decimal and Float data types , if im
writing accounting application and use 10:4 as precision/scale in all
numbers , does it matters if i choose fields as Decimal or Float in that
case ?
in BOL its says about float that
Approximate number data types for use with floating point numeric data.
Floating point data is approximate; not all values in the data type range
can be precisely represented.
i do not know what that means? can anyone give small example adding 2
different numbers that will give different answers if column type is decimal
than float ?
Best Regards
Bassamfloats store numbers as base 2, decimals as base 10. You can lookup a full
explaination on google, I would not do it justice. Tey this to see how they
differ.
create table Test
(NumDecimal decimal(10,4)
, numFloat float
);
insert into Test (NumDecimal, numFloat) values (0.1, 0.1);
insert into Test (NumDecimal, numFloat) values (0.3, 0.3);
insert into Test (NumDecimal, numFloat) values (0.25, 0.25);
insert into Test (NumDecimal, numFloat) values (1.0/3.0, 1.0/3.0);
insert into Test (NumDecimal, numFloat) values (1.0/6.0, 1.0/6.0);
select * from test;
select numdecimal*3 , numfloat*3 from test;
drop table Test;
"Bassam" <bassam@.nptco.com.eg> wrote in message
news:OpLEDirbGHA.2456@.TK2MSFTNGP04.phx.gbl...
> Hello, sorry if this question is silly
> I'm

about difference between Decimal and Float data types , if im
> writing accounting application and use 10:4 as precision/scale in all
> numbers , does it matters if i choose fields as Decimal or Float in that
> case ?
> in BOL its says about float that
> Approximate number data types for use with floating point numeric data.
> Floating point data is approximate; not all values in the data type range
> can be precisely represented.
> i do not know what that means? can anyone give small example adding 2
> different numbers that will give different answers if column type is
> decimal than float ?
Run this in Query Analyzer:
declare @.d1 decimal(10, 4), @.d2 decimal(10, 4),
@.f1 float, @.f2 float
SELECT @.d1 = 98.234, @.d2 = 87.0987
SELECT @.f1 = 98.234, @.f2 = 87.0987
SELECT @.d1 = 98.234, @.d2 = 87.0987
SELECT @.d1 + @.d2, @.f1 + @.f2
More generally, while is valid and reasonable to write:
WHERE decimalcol = 0
the same is not true for
WHERE floatcal = 0
Because due to rounding errors, floatcol may have a value like
0.0000000000000123
It's possible to use float in an accounting application, but you have to
be very careful. Decimal has its pitfalls too, but is probably safer.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||In an accounting application, exact values (decimal data type) should be
used.
For float and real data types, they are approximate as it a the specified
number of bits to store the mantissa of the float number in scientific
notation, in binary form. The conversion of a decimal value (with decimal
places) to a binary value usually results in a lost of precision.
Try the following.
select convert(decimal(10, 4), 111.1111) as MyDecimalValue,
convert(float(24), 111.1111) as MyFloatValue
Martin C K Poon
Senior Analyst Programmer
====================================
"Bassam" <bassam@.nptco.com.eg> bl
news:OpLEDirbGHA.2456@.TK2MSFTNGP04.phx.gbl g...
> Hello, sorry if this question is silly
> I'm

about difference between Decimal and Float data types , if im
> writing accounting application and use 10:4 as precision/scale in all
> numbers , does it matters if i choose fields as Decimal or Float in that
> case ?
> in BOL its says about float that
> Approximate number data types for use with floating point numeric data.
> Floating point data is approximate; not all values in the data type range
> can be precisely represented.
> i do not know what that means? can anyone give small example adding 2
> different numbers that will give different answers if column type is
decimal
> than float ?
> --
> Best Regards
> Bassam
>
>|||you know, when running this in Management Studio I get
@.fa + @.fb = 8.6
@.da + @.db = 8.600
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23VAvd2rbGHA.2396@.TK2MSFTNGP02.phx.gbl...
>
> Run below in Query Analyzer and you will see:
> DECLARE @.fa float, @.fb float, @.da decimal(10,4), @.db decimal(10,4)
> SELECT @.fa = 3.1, @.da = 3.1
> SELECT @.fb = 5.5, @.db = 5.5
> SELECT @.fa + @.fb
> SELECT @.da + @.db
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Bassam" <bassam@.nptco.com.eg> wrote in message
> news:OpLEDirbGHA.2456@.TK2MSFTNGP04.phx.gbl...|||Presenting the values returned (in binary format) from SQL Server is the tas
k of the client
application. Apparently, SSMS assumes that you aren't that concerned about a
ll the decimals when you
use float and real, while QA is more exact in the representation of these va
lues.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve" <ss@.Mailinator.com> wrote in message news:ufjgO$rbGHA.3800@.TK2MSFTNGP04.phx.gbl...[
color=darkred]
> you know, when running this in Management Studio I get
> @.fa + @.fb = 8.6
> @.da + @.db = 8.600
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23VAvd2rbGHA.2396@.TK2MSFTNGP02.phx.gbl...
>[/color]|||For completeness... what are the pitfalls of the Decimal datatype? I've
always found that as long as I'm careful with the precision the results
are accurate.|||I'd rather have the results from SSMS include all the decimals. Anyway to
force that, or is there an option to change?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OAme4FsbGHA.3388@.TK2MSFTNGP05.phx.gbl...
> Presenting the values returned (in binary format) from SQL Server is the
> task of the client application. Apparently, SSMS assumes that you aren't
> that concerned about all the decimals when you use float and real, while
> QA is more exact in the representation of these values.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve" <ss@.Mailinator.com> wrote in message
> news:ufjgO$rbGHA.3800@.TK2MSFTNGP04.phx.gbl...
>sql

float vs decimal

select convert(float,'1.2334e+006')
1233400.0

select convert(decimal(20,2),'1.2334e+006')
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Is there any way around?
Is there any set options? I tried arithabort or arithignore and they
don't work.
Thanks.(othellomy@.yahoo.com) writes:

Quote:

Originally Posted by

select convert(float,'1.2334e+006')
1233400.0
>
select convert(decimal(20,2),'1.2334e+006')
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
>
Is there any way around?
Is there any set options? I tried arithabort or arithignore and they
don't work.

1.2334e+006 is not a legal literal for decimal. You will have to convert
in two steps, first to float, then to decimal.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Am 21 Nov 2006 22:09:05 -0800 schrieb othellomy@.yahoo.com:

Quote:

Originally Posted by

select convert(decimal(20,2),cast('1.2334e+006' as float))

bye,
Helmut

Float type

I inhereted a database that has a lot of Float type columns. Typically
these columns hold decimal-type data rounded to four decimal points
such as:
987.1234
Is Float the appropriate data type for use with these types of columns?
Float is generally referred to as an approximate datatype (approximate for the 10.base system, which
us humans tend to use). This mean that a value you input might not be the one which is stored, just
try below:
SELECT CAST(3.1 AS float)
If above is not acceptable, then use NUMERIC with a scale of 4 instead, like:
SELECT CAST(3.1 AS numeric(9,4))
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"laurenq uantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1135913496.671119.91530@.g49g2000cwa.googlegro ups.com...
>I inhereted a database that has a lot of Float type columns. Typically
> these columns hold decimal-type data rounded to four decimal points
> such as:
> 987.1234
> Is Float the appropriate data type for use with these types of columns?
>

Float type

I inhereted a database that has a lot of Float type columns. Typically
these columns hold decimal-type data rounded to four decimal points
such as:
987.1234
Is Float the appropriate data type for use with these types of columns?Float is generally referred to as an approximate datatype (approximate for t
he 10.base system, which
us humans tend to use). This mean that a value you input might not be the on
e which is stored, just
try below:
SELECT CAST(3.1 AS float)
If above is not acceptable, then use NUMERIC with a scale of 4 instead, like
:
SELECT CAST(3.1 AS numeric(9,4))
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"laurenq uantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1135913496.671119.91530@.g49g2000cwa.googlegroups.com...
>I inhereted a database that has a lot of Float type columns. Typically
> these columns hold decimal-type data rounded to four decimal points
> such as:
> 987.1234
> Is Float the appropriate data type for use with these types of columns?
>

Float type

I inhereted a database that has a lot of Float type columns. Typically
these columns hold decimal-type data rounded to four decimal points
such as:
987.1234
Is Float the appropriate data type for use with these types of columns?Float is generally referred to as an approximate datatype (approximate for the 10.base system, which
us humans tend to use). This mean that a value you input might not be the one which is stored, just
try below:
SELECT CAST(3.1 AS float)
If above is not acceptable, then use NUMERIC with a scale of 4 instead, like:
SELECT CAST(3.1 AS numeric(9,4))
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"laurenq uantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1135913496.671119.91530@.g49g2000cwa.googlegroups.com...
>I inhereted a database that has a lot of Float type columns. Typically
> these columns hold decimal-type data rounded to four decimal points
> such as:
> 987.1234
> Is Float the appropriate data type for use with these types of columns?
>

float to decimal without rounding

select 'Customer_Greeting_Section_total'=
Case when
Customer_Greeting_Section_total is null then 0.00
else left (ROUND(Customer_Greeting_Section_Total , 2, 1),4)
end
from DTSTEMP1
The col is defined as float
When I run this I get
Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.Hi
It is not clear why you are using left, as this a string function as doing
this may be meaningless. Removing the left function will (probably) stop the
error. See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL an
d
example data will help to solve your problem. Posting your desired results
from the query is also useful.
John
"Disney" wrote:

> select 'Customer_Greeting_Section_total'=
> Case when
> Customer_Greeting_Section_total is null then 0.00
> else left (ROUND(Customer_Greeting_Section_Total , 2, 1),4)
> end
> from DTSTEMP1
> The col is defined as float
> When I run this I get
> Server: Msg 8115, Level 16, State 8, Line 1
> Arithmetic overflow error converting numeric to data type numeric.sql

Float to Decimal Conversion - Transaction Log Fills Up

Hi,
I am trying to change multiple columns on multiple tables to decimal
28,8 from float. Everytime I attempt to do this, the transaction log
fills up and the process stops. I have attempted this via T-SQL all to
no avail.
What is the correct way of doing this?
SQL Server 7 is the version being used.
Thanks,
Tony.
If the tables are small enough, do only one table then backup the
transaction log. If the tables are still too large, you may have to create
another table with the intended datatypes, insert data in stages, backing up
the log after each stage. Then, drop the original table and rename the new
table.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"T Kennedy" <tony.kennedy@.intl.pepsico.com> wrote in message
news:ebc9c5a7.0502010311.54fd362d@.posting.google.c om...
Hi,
I am trying to change multiple columns on multiple tables to decimal
28,8 from float. Everytime I attempt to do this, the transaction log
fills up and the process stops. I have attempted this via T-SQL all to
no avail.
What is the correct way of doing this?
SQL Server 7 is the version being used.
Thanks,
Tony.
|||To add on to Tom's response, you can avoid filling the log if you have the
'select into' database option on (SIMPLE/BULK_LOGGED recovery model in SQL
2000) by creating a new table with a minimally-logged SELECT ... INTO. You
may need to add ISNULL to coerce NOT NULL for converted columns that are NOT
NULL in the source table.
CREATE TABLE MyTable
(
Col1 float not null,
Col2 float null
)
GO
SELECT
ISNULL(CAST(Col1 AS decimal(28, 8)), 0) AS Col1,
CAST(Col2 AS decimal(28, 8)) AS Col2
INTO MyTable_New
FROM MyTable
GO
DROP TABLE MyTable
EXEC sp_rename 'MyTable_New', 'MyTable'
--recreate constraints and indexes
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"T Kennedy" <tony.kennedy@.intl.pepsico.com> wrote in message
news:ebc9c5a7.0502010311.54fd362d@.posting.google.c om...
> Hi,
> I am trying to change multiple columns on multiple tables to decimal
> 28,8 from float. Everytime I attempt to do this, the transaction log
> fills up and the process stops. I have attempted this via T-SQL all to
> no avail.
> What is the correct way of doing this?
> SQL Server 7 is the version being used.
> Thanks,
> Tony.

Float to Decimal Conversion - Transaction Log Fills Up

Hi,
I am trying to change multiple columns on multiple tables to decimal
28,8 from float. Everytime I attempt to do this, the transaction log
fills up and the process stops. I have attempted this via T-SQL all to
no avail.
What is the correct way of doing this?
SQL Server 7 is the version being used.
Thanks,
Tony.If the tables are small enough, do only one table then backup the
transaction log. If the tables are still too large, you may have to create
another table with the intended datatypes, insert data in stages, backing up
the log after each stage. Then, drop the original table and rename the new
table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"T Kennedy" <tony.kennedy@.intl.pepsico.com> wrote in message
news:ebc9c5a7.0502010311.54fd362d@.posting.google.com...
Hi,
I am trying to change multiple columns on multiple tables to decimal
28,8 from float. Everytime I attempt to do this, the transaction log
fills up and the process stops. I have attempted this via T-SQL all to
no avail.
What is the correct way of doing this?
SQL Server 7 is the version being used.
Thanks,
Tony.|||To add on to Tom's response, you can avoid filling the log if you have the
'select into' database option on (SIMPLE/BULK_LOGGED recovery model in SQL
2000) by creating a new table with a minimally-logged SELECT ... INTO. You
may need to add ISNULL to coerce NOT NULL for converted columns that are NOT
NULL in the source table.
CREATE TABLE MyTable
(
Col1 float not null,
Col2 float null
)
GO
SELECT
ISNULL(CAST(Col1 AS decimal(28, 8)), 0) AS Col1,
CAST(Col2 AS decimal(28, 8)) AS Col2
INTO MyTable_New
FROM MyTable
GO
DROP TABLE MyTable
EXEC sp_rename 'MyTable_New', 'MyTable'
--recreate constraints and indexes
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"T Kennedy" <tony.kennedy@.intl.pepsico.com> wrote in message
news:ebc9c5a7.0502010311.54fd362d@.posting.google.com...
> Hi,
> I am trying to change multiple columns on multiple tables to decimal
> 28,8 from float. Everytime I attempt to do this, the transaction log
> fills up and the process stops. I have attempted this via T-SQL all to
> no avail.
> What is the correct way of doing this?
> SQL Server 7 is the version being used.
> Thanks,
> Tony.

Float to Decimal Conversion - Transaction Log Fills Up

Hi,
I am trying to change multiple columns on multiple tables to decimal
28,8 from float. Everytime I attempt to do this, the transaction log
fills up and the process stops. I have attempted this via T-SQL all to
no avail.
What is the correct way of doing this?
SQL Server 7 is the version being used.
Thanks,
Tony.If the tables are small enough, do only one table then backup the
transaction log. If the tables are still too large, you may have to create
another table with the intended datatypes, insert data in stages, backing up
the log after each stage. Then, drop the original table and rename the new
table.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"T Kennedy" <tony.kennedy@.intl.pepsico.com> wrote in message
news:ebc9c5a7.0502010311.54fd362d@.posting.google.com...
Hi,
I am trying to change multiple columns on multiple tables to decimal
28,8 from float. Everytime I attempt to do this, the transaction log
fills up and the process stops. I have attempted this via T-SQL all to
no avail.
What is the correct way of doing this?
SQL Server 7 is the version being used.
Thanks,
Tony.|||To add on to Tom's response, you can avoid filling the log if you have the
'select into' database option on (SIMPLE/BULK_LOGGED recovery model in SQL
2000) by creating a new table with a minimally-logged SELECT ... INTO. You
may need to add ISNULL to coerce NOT NULL for converted columns that are NOT
NULL in the source table.
CREATE TABLE MyTable
(
Col1 float not null,
Col2 float null
)
GO
SELECT
ISNULL(CAST(Col1 AS decimal(28, 8)), 0) AS Col1,
CAST(Col2 AS decimal(28, 8)) AS Col2
INTO MyTable_New
FROM MyTable
GO
DROP TABLE MyTable
EXEC sp_rename 'MyTable_New', 'MyTable'
--recreate constraints and indexes
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"T Kennedy" <tony.kennedy@.intl.pepsico.com> wrote in message
news:ebc9c5a7.0502010311.54fd362d@.posting.google.com...
> Hi,
> I am trying to change multiple columns on multiple tables to decimal
> 28,8 from float. Everytime I attempt to do this, the transaction log
> fills up and the process stops. I have attempted this via T-SQL all to
> no avail.
> What is the correct way of doing this?
> SQL Server 7 is the version being used.
> Thanks,
> Tony.

2012年3月27日星期二

Float or Decimal?

When should I choose decimal over float and vice versa?
Mike BYou should always use a decimal if your data is definable. If it's not and you requiret the ability to perform float operations, you should use float. This should be rare or never. It's better to define the scope of the data in the planning stage.|||I use DECIMAL for things that are really counts of something, like money. I use FLOAT for things that are measures, like distance or most kinds of weight. The two usages are fundamentally different, and the implementations (both from a storage and a manipulation perspective) are different too. It rarely works well if you use the wrong one!

-PatP|||Yeah, you don't really want your accounting application to use FLOAT. (grin)|||Yeah, you don't really want your accounting application to use FLOAT. (grin)Now that isn't entirely true!

If you work for the kind of business that thinks about money in terms of "3 inches of $50 bills", then I'd have no problem with using a FLOAT. This would also make it lots easier to handle the conversion between money and grams of product too!

I don't know of anybody that does accounting that way, but if they did then I'd be fine with the idea of using reals in that particular accounting system! ;) The rest of us will have to make do with using more conventional things like MONEY or DECIMAL columns.

-PatP

FLOAT

I want to use only float datatype but the problem is that it return more
than two decimal, I want to minimize to two decimal points? I don't want to
use decimal datatype... I only wanna use float....
The result should looks like
152254.45
but now it's giving me.
152254.45123232363
Any solution ?
Thanks
On Wed, 2 Nov 2005 11:19:15 -0500, Rogers wrote:

>I want to use only float datatype but the problem is that it return more
>than two decimal, I want to minimize to two decimal points? I don't want to
>use decimal datatype... I only wanna use float....
>The result should looks like
>152254.45
>but now it's giving me.
>152254.45123232363
>Any solution ?
Hi Rogers,
No, of course not. The FLOAT datatype is designed to *not* have an exact
and limited number of decimals. The datatypes that are designed to offer
a fixed number of decimal places are decimal and numeric.
Saying "I want to have two decimal points but I don't want to use
decimal datatype... I olnly wanna use float" is like saying "I want to
travel by air but I don't want to use a plane... I only wanna use a
boat".
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||I only want to use float datatype because I have to frequenly convert the
database into Access 97 and Access doesn't support decimal and numeric
datatype.
Thanks
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:q43im1ddb79j8bmre6kugqrjo7nem75f6r@.4ax.com...
> On Wed, 2 Nov 2005 11:19:15 -0500, Rogers wrote:
>
> Hi Rogers,
> No, of course not. The FLOAT datatype is designed to *not* have an exact
> and limited number of decimals. The datatypes that are designed to offer
> a fixed number of decimal places are decimal and numeric.
> Saying "I want to have two decimal points but I don't want to use
> decimal datatype... I olnly wanna use float" is like saying "I want to
> travel by air but I don't want to use a plane... I only wanna use a
> boat".
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||Rogers wrote:
> I only want to use float datatype because I have to frequenly convert
> the database into Access 97 and Access doesn't support decimal and
> numeric datatype.
>
Access 2003 ( I realize this is not Access 97 - but that version is almost 9
years old) has a Number Data Type, which can then be qualified with the
"Number" type. Choice abound:
- Byte
- Integer
- Long Integer
- Single
- Double
- Replication ID
- Decimal - which has precision and scale to match that used by SQL Server
David Gugick
Quest Software
|||On Wed, 2 Nov 2005 14:47:07 -0500, Rogers wrote:

>I only want to use float datatype because I have to frequenly convert the
>database into Access 97 and Access doesn't support decimal and numeric
>datatype.
Hi Rogers,
I just did a quick test - create a table with a decimal(9,2) column in
SQL Server, then create a linked table in Access 97. Access defines the
column as double. I could enter and retrieve data without any problems
(other than data with more than two decimals being rounded).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
sql

2012年3月22日星期四

Flat File import question

I have a fixed width flat file that I'm trying to import, and I'm just about there. The last column that I'm struggling with, is a decimal amount. The data in the column looks like this 00000000500 and I need to dump it into a column as 5.000 In otherwords, the data in the file does not have any decimals, and I'm putting it into a sql server column that has the datatype numeric(11,4) I've set the InputColumnWidth to 11, the DataPrecision to 11 and the datascale to 2, and the value is still being imported as 500.000 Is there any way to achieve this other than using a script component to calculate the value? Thanks!

tee bone wrote:
I have a fixed width flat file that I'm trying to import, and I'm just about there. The last column that I'm struggling with, is a decimal amount. The data in the column looks like this 00000000500 and I need to dump it into a column as 5.000 In otherwords, the data in the file does not have any decimals, and I'm putting it into a sql server column that has the datatype numeric(11,4) I've set the InputColumnWidth to 11, the DataPrecision to 11 and the datascale to 2, and the value is still being imported as 500.000 Is there any way to achieve this other than using a script component to calculate the value? Thanks!

Add a derived column to take that value and divide it by 100 (or 1000, if you need)|||Thanks for the quick reply, so in other words there's no actual way to handle this situation in the connection manager itself? The solution that you have proposed will work, I was just trying to cut down on the time it takes for the package to process. We have to load a daily file that's about 300mb, so it takes a long time to process. I've cut it down to under a minute, but I was afraid adding another step in the flow would add quite a bit of time. I'm pretty new to SSIS, so please let me know if I'm worrying about it for no reason. Thanks!
|||On a side note, I'd go try this out and test it out for myself, but I'm going to have to apply this derived column transformation on about 150 columns, so I'm trying to get a good idea of what to expect before I get started
|||Yeah, the connection manager just reads the data as it is. What it currently sees (without a decimal point) is an integer. It is what it is. Either fix it in the source, or use a derived column. Sucks, yes, but that's the way it is.|||Sounds good, thanks for the help!

2012年3月11日星期日

fixed point arithmetic for price calculations

I've got a price in euro as a string, which I can easily cast to a numeric SSIS data type e.g. R4, R8, DECIMAL, NUMERIC. And I've got the dollar/euro exchange rate stored in an SSIS variable of type DOUBLE, set to 1.28 for testing purposes. I want to multiply the two values and return the (dollar) result, rounded (not truncated) to 2 decimal places, as a string.

Here are some experiments I did in an SSIS expression editor:

(DT_WSTR, 10) (1.28 * 31.10) evaluates to "39.8080"

(DT_WSTR, 10) (1.28 * (DT_R8) "31.10") evaluates to "39.808"

(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 0) "31.10") evaluates to "39.68"

(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 1) "31.10") evaluates to "39.808"

(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 2) "31.10") evaluates to "39.8080"

(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 3) "31.10") evaluates to "39.80800"

Of course, what I really want is "39.81", so I went on:

(DT_WSTR, 10) ((DT_DECIMAL, 0) (1.28 * (DT_R8) "31.10")) evaluates to "39"

(DT_WSTR, 10) ((DT_DECIMAL, 1) (1.28 * (DT_R8) "31.10")) evaluates to "39.8"

This looks promising! But:

(DT_WSTR, 10) ((DT_DECIMAL, 2) (1.28 * (DT_R8) "31.10")) evaluates to "39.8"

(DT_WSTR, 10) ((DT_DECIMAL, 3) (1.28 * (DT_R8) "31.10")) evaluates to "39.808"

Argh... How does one get a floating point value rounded to 2 decimal places?

(DT_NUMERIC, 6,2)(1.28 * 31.10) ?|||

Phil Brammer wrote:
(DT_NUMERIC, 6,2)(1.28 * 31.10) ?

Hmm... That didn't seem to work either.|||Use the ROUND() function. That will work for you.

ROUND(1.28 * 31.1 ,2)|||

(DT_WSTR, 10) ROUND(1.28 * 31.10, 2) indeed evaluates to "39.8100", which can simply be truncated.

I didn't think to try ROUND(numeric_expression, length) because the help text in the Expression Builder says that it returns an integer (regardless of the length parameter).

Thanks!

|||

Kevin Rodgers wrote:

(DT_WSTR, 10) ROUND(1.28 * 31.10, 2) indeed evaluates to "39.8100", which can simply be truncated.

Hmmm. I had to increase the string length from 10 to avoid a truncation error, so I changed it to 4000 which I think is the maximum for Unicode strings -- no worries. But more testing reveals that (DT_WSTR, 4000) ROUND(numeric_expression, 2) sometimes returns a value with fewer than 2 digits after the decimal point e.g. "16" instead of "16.00" or "133.5" insead of "133.50"'.

Here's what I'm using to ensure that there is a decimal point followed by 2 digits in the result:

FINDSTRING(usd_price, ".", 1) > 0 ? SUBSTRING(usd_price + "00", 1, FINDSTRING(usd_price, ".", 1) + 2) : usd_price + ".00"

Fixed decimal convertion

I am trying to show latitude and longitude with 5 decimal points. Now its showing (for example: 55.744025477, -4.1256633333333 etc.). How do I get data in 5 decimal points?

Your help with example would be appreciated.

aspx code:

<asp:GridView ID="GridView1" runat="server" DataSourceID="odsGPS" AllowPaging="true" AllowSorting="true"
AutoGenerateColumns="False" CellPadding="1" CellSpacing="1" BackColor="White" GridLines="None"
BorderColor="White" BorderStyle="Ridge" BorderWidth="2px" PageSize="20" Width="100%" Font-Size="8pt"
OnLoad="GridView1_Load" >
<Columns>
<asp:TemplateField HeaderText="Show">
<ItemTemplate>
<asp:CheckBox ID="CheckBox2" onclick="MarkerForThisRow(this);" ToolTip="Click to show on map." runat="server" OnCheckedChanged="CheckBox2_CheckedChanged" />
</ItemTemplate>
<ItemStyle HorizontalAlign="Center" />
<HeaderStyle HorizontalAlign="Center" />
</asp:TemplateField>

<asp:BoundField DataField="Latitude" HeaderText="Latitude ( ° )" >
<ItemStyle HorizontalAlign="Center" />
<HeaderStyle HorizontalAlign="Center" />
</asp:BoundField>

<asp:BoundField DataField="Longitude" HeaderText="Longitude ( ° )" >
<ItemStyle HorizontalAlign="Center" />
<HeaderStyle HorizontalAlign="Center" />
</asp:BoundField>

In your <asp:BoundField /> for the Latitude, add the following:

DataFormatString="{0:F5}"

F represents a fixed decimal, and 5 represents the number of decimal places

|||

On your gridview open the gridview task with clicking little arrow on the right upper corner.

Then click Edit Columns
Then select your field "Latitude",
Then on the right, scroll down and come to Data section.
Change the dataFormatString as {0:N5}
Also you will see another property under Behavior section which is "HtmlEncode" make it false...

Let me know if it does not work...

|||

Thanks a lot guys for your help.

In the mean time, I found another way to do it. Using Convert.ToDecimal in a Item Template also does the job.

<asp:TemplateField HeaderText="Latitude ( ° )">
<ItemTemplate>
<%# Convert.ToDecimal(Eval("Latitude")).ToString("0.00000") %>
</ItemTemplate></asp:TemplateField>

|||

Hiavci,

It did not work in my case.

|||

Hi,

It must work.I already tried and it's working.

Did you make HTMLEncode=False ?|||

I did. But may be I am missing something else.

<asp:BoundField DataField="Latitude" HeaderText="Latitude " DataFormatString="{0:N5}" HtmlEncode="False" >
<ItemStyle HorizontalAlign="Center" />
<HeaderStyle HorizontalAlign="Center" />
</asp:BoundField>

|||

Datafield "Latitude" is not defined varchar or text in the database,right?
Can you check its type in the database?

|||

GPSID int Unchecked
VehicleID int Unchecked
Latitude varchar(50) Unchecked
Longitude varchar(50) Unchecked
GPSTime datetime Checked
ServerTime datetime Unchecked
Valid bit Unchecked

|||

If those two are varchars, you will not be able to write them as decimals.That property we said will not work. You have to change their type to int, or decimal.

订阅：博文 (Atom)

2012年3月29日星期四

floor function

floating point truncation

floating point calculation

float vs decimal - computation time

Float vs Decimal

float vs decimal

Float type

Float type

Float type

float to decimal without rounding

Float to Decimal Conversion - Transaction Log Fills Up

Float to Decimal Conversion - Transaction Log Fills Up

Float to Decimal Conversion - Transaction Log Fills Up

2012年3月27日星期二

Float or Decimal?

FLOAT

2012年3月22日星期四

Flat File import question

2012年3月11日星期日

fixed point arithmetic for price calculations

Fixed decimal convertion

FTS Index Size

Blog Archive

About Me