Datatype question

What is the difference between decimal(9) and decimal(9,0)?
Thanks!There is no difference, because the default scale is 0.
For more informations, see:
http://msdn.microsoft.com/library/e...des_04_82ic.asp
Razvan|||thanks!
"Razvan Socol" wrote:

> There is no difference, because the default scale is 0.
> For more informations, see:
> http://msdn.microsoft.com/library/e...des_04_82ic.asp
> Razvan
>

Datatype Question

I've got numbers stored in my table as varchar which i need to cast to a
datatype so that i can perform an arithmetic operation on.
34.2348905444367
45.08070345435
34.6546456354354353
43.6540697929
the problem is, I need them to be cast so that i can perform calculations on
them. I need the specific value back, and not something that is rounded up
or down.
If I use float I get extra numbers appearing at the end, (ie. there is a
rounding issue)
If I try to cast as decimal(12, 16)
The scale must be less than or equal to the precision.
How can i simply get the true value of the record ?
Many thanks.decimal(28, 16)
28 = total length
16 = positions to the right of the decimal point.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Douglas Adams" wrote:

> I've got numbers stored in my table as varchar which i need to cast to a
> datatype so that i can perform an arithmetic operation on.
> 34.2348905444367
> 45.08070345435
> 34.6546456354354353
> 43.6540697929
> the problem is, I need them to be cast so that i can perform calculations
on
> them. I need the specific value back, and not something that is rounded u
p
> or down.
> If I use float I get extra numbers appearing at the end, (ie. there is a
> rounding issue)
>
> If I try to cast as decimal(12, 16)
> The scale must be less than or equal to the precision.
>
> How can i simply get the true value of the record ?
> Many thanks.
>
>|||The following works for me. But don't confuse how the number is
*displayed* with the way it is stored
CREATE TABLE T1 (x VARCHAR(20) NOT NULL PRIMARY KEY)
INSERT INTO T1 (x)
SELECT 34.2348905444367 UNION ALL
SELECT 45.08070345435 UNION ALL
SELECT 34.6546456354354353 UNION ALL
SELECT 43.6540697929 ;
SELECT CAST(x AS DECIMAL(20,16)) FROM T1
Result:
34.2348905444367000
34.6546456354354353
43.6540697929000000
45.0807034543500000
(4 row(s) affected)
David Portas
SQL Server MVP
--|||Thanks guys
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1121852998.202064.66570@.g43g2000cwa.googlegroups.com...
> The following works for me. But don't confuse how the number is
> *displayed* with the way it is stored
> CREATE TABLE T1 (x VARCHAR(20) NOT NULL PRIMARY KEY)
> INSERT INTO T1 (x)
> SELECT 34.2348905444367 UNION ALL
> SELECT 45.08070345435 UNION ALL
> SELECT 34.6546456354354353 UNION ALL
> SELECT 43.6540697929 ;
> SELECT CAST(x AS DECIMAL(20,16)) FROM T1
> Result:
> --
> 34.2348905444367000
> 34.6546456354354353
> 43.6540697929000000
> 45.0807034543500000
> (4 row(s) affected)
> --
> David Portas
> SQL Server MVP
> --
>

Datatype problem?

Hello
We ahve a problem with datatypes. I the cube whe have 2 mesasures (Item_Sold and Sales). Item_sold is Numeric datatype and Sales is Currency. In the virtual cube whe divide the to in to a average price (Sales/Item_sold).
We user Excel show the cube.
For integer numbers we get no problem but for item_sold with decimal values (like 1,43) the division get currupt. It seems that it only devide the integer so average measures of:
Item_sold=2,5
Sales=500
...will be 250 (500/2). And if the item_sold is smaller than 1 (for units smaller than 1 kilo) the average value will even more strange.

I think that the datatyp is the error. What datatyp shall i use?

I am also getting this strange problem

What is happening is that when something is being divided by 0.XX its producing those wrong figures where decimal is at a wrong place. But if we divide the numerator and denominator by 100, we get the correct figures.

Something *100 / 0.XX *100 = good

Something / 0.XX = wrong

|||

OK,

I found the solution. Its related to data types. Make sure that both the denominator and the numenator are of same data type (may be numberic)

It solved my problem

sql

Datatype problem?

Hello
We ahve a problem with datatypes. I the cube whe have 2 mesasures (Item_Sold and Sales). Item_sold is Numeric datatype and Sales is Currency. In the virtual cube whe divide the to in to a average price (Sales/Item_sold).
We user Excel show the cube.
For integer numbers we get no problem but for item_sold with decimal values (like 1,43) the division get currupt. It seems that it only devide the integer so average measures of:
Item_sold=2,5
Sales=500
...will be 250 (500/2). And if the item_sold is smaller than 1 (for units smaller than 1 kilo) the average value will even more strange.

I think that the datatyp is the error. What datatyp shall i use?

I am also getting this strange problem

What is happening is that when something is being divided by 0.XX its producing those wrong figures where decimal is at a wrong place. But if we divide the numerator and denominator by 100, we get the correct figures.

Something *100 / 0.XX *100 = good

Something / 0.XX = wrong

|||

OK,

I found the solution. Its related to data types. Make sure that both the denominator and the numenator are of same data type (may be numberic)

It solved my problem

Datatype problem?

Hello
We ahve a problem with datatypes. I the cube whe have 2 mesasures (Item_Sold and Sales). Item_sold is Numeric datatype and Sales is Currency. In the virtual cube whe divide the to in to a average price (Sales/Item_sold).
We user Excel show the cube.
For integer numbers we get no problem but for item_sold with decimal values (like 1,43) the division get currupt. It seems that it only devide the integer so average measures of:
Item_sold=2,5
Sales=500
...will be 250 (500/2). And if the item_sold is smaller than 1 (for units smaller than 1 kilo) the average value will even more strange.

I think that the datatyp is the error. What datatyp shall i use?

I am also getting this strange problem

What is happening is that when something is being divided by 0.XX its producing those wrong figures where decimal is at a wrong place. But if we divide the numerator and denominator by 100, we get the correct figures.

Something *100 / 0.XX *100 = good

Something / 0.XX = wrong

|||

OK,

I found the solution. Its related to data types. Make sure that both the denominator and the numenator are of same data type (may be numberic)

It solved my problem

Datatype problem: development vs production servers

This is driving me nuts: On my development machine the code runs finebut generates an error on the production server. Both are running SQLServer 2000 and ASP.NET 1.1

The datatype of the field in question isdatetime.
The webform has a calendar for a user to select and automaticallyinsert the date into the textbox. The update command in the webform is:
cmdInsert.Parameters.Add("@.citation_date", CDate(txtDate.Text))

This works without a hitch on my development system, but on the production server it generates the following error:
Cast from string "19-12-1997" to type 'Date' is not valid.

WHY?
It has to do with the locale information (country, language, etc.) for the computer. Check to make sure the server is set to whatever you're using on your local development PC. I'm not familiar with setting/changing these since I only use U.S. format and English.|||

jcasp wrote:

It has to do with the locale information (country,language, etc.) for the computer. Check to make sure the serveris set to whatever you're using on your local development PC. I'mnot familiar with setting/changing these since I only use U.S. formatand English.

You are right. I'm inputting U.S format of date for the time being until I've figured a way around it. Thanks!|||Use YYYY-MM-DD format, then it doesn't matter what culture you are in.

DATATYPE PROBLEM(cross)

I have a column in my table BizdekiFiyat . The datatype = float length =8
(to save money values).. It is impossible to change these attributes for
some reasons.
It has records like This
BizdekiFiyat
110
24
29.5
31.35
I use Vb.Net . I use ExecuteReader To select values from my db..
After first attemp
Dim BizdekiFiyat As Integer OR Dim BizdekiFiyat As Decimal
IT returns
110
24
295
3135
Dim BizdekiFiyat As String
It returns right results.
110
24
29.5
31.35
There is a problem with decimal records when i want to evaluate this
values..
For example
Dim BizdekiFiyat As String
BizdekiFiyat = BizdekiFiyat * 1.05
It is supposed to be
29.5 * 1.05 =30.975
31.35*1.05=32.9175
but it returns
309,75
3291,75
How can i solve this problem ?"Savas Ates" <in da club> wrote in message
news:OEhAfpwLGHA.648@.TK2MSFTNGP14.phx.gbl...
>I have a column in my table BizdekiFiyat . The datatype = float length =8
>(to save money values).. It is impossible to change these attributes for
>some reasons.
>
> Dim BizdekiFiyat As Integer OR Dim BizdekiFiyat As Decimal
> IT returns
> 110
> 24
> 295
> 3135
Integer datatype will always truncate your decimal fraction values.
I've had data dimension problems trying to use the Decimal datatype for
holding (SQL) decimal data returned through parameters using MS's EntLib
DAAB. I resolved this by using .NET's Double datatype (though I'd prefer to
know why .NET's decimal gave me the problem in the first place).

> Dim BizdekiFiyat As String
> It returns right results.
Because the value is being represented and stored as a string (just like
typing into a textbox), not a numeric type, so...

> There is a problem with decimal records when i want to evaluate this
> values..
> For example
> Dim BizdekiFiyat As String
> BizdekiFiyat = BizdekiFiyat * 1.05
> How can i solve this problem ?
You're expecting .NET to intelligently convert your datatypes for you, which
it is valiantly trying to do. You should consider setting Option Strict on
(Tools | Options | Projects | VB Defaults) to prevent loose data typing and
late binding. You should strongly type your datatypes as a matter of god
practice. When you need to convert datatypes, dothis explicitely using
CType(sourceObj, targetType), or the shorthand versions such as Cint(value),
CDbl(value), etc.
As for your calculations: e.g. using Double to store your values, create a
function which you'll call when necessary to do your calculations:
private function MultiplyBizdekiFiyat(Byval origValue as double, Byval
MultiplyBy as double) As Double
'Perform the calculation
MultiplyBizdekiFiyat = origValue * MultiplyBy
'Return the value to the calling method.
return MultiplyBizdekiFiyat
end function
Hope that helps
Al