regarding data types convertion in sql server 2000

Question

Post reply

regarding data types convertion in sql server 2000

kamran_hassan_anjum

SSC Rookie

Points: 28
More actions
September 9, 2008 at 2:36 pm

#123507

Hi,
can anyone help me here for converting decimal datatype into numeric or decimal with changed precision and scale in sql server 2000
thanks.
i really appreciate that.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 738693 More actions · Answer 1

Steve Jones - SSC Editor

SSC Guru

Points: 738693

September 9, 2008 at 9:25 pm

#869383

Use CAST to convert data types.

kamran_hassan_anjum SSC Rookie Points: 28 More actions · Answer 2

Hi,

But how, I really didn't get it . I have tried it so many time but giving me errors. can you tell me any other way please?

thanks

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 3

Can you post your tries so that we can help?

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Steve Jones - SSC Editor SSC Guru Points: 738693 More actions · Answer 4

CAST works, but without seeing data or errors (or attempts) we have no way to help.

The error messages are usually obvious. You might not like what you're seeing, but they describe the issue.

kamran_hassan_anjum SSC Rookie Points: 28 More actions · Answer 5

Hi,

thanks for your response david, here is the cast function below i am trying but giving expected ')'.

Function Main()

CAST(DTSSource("ORDER_QTY") AS numeric(9)) * CAST(DTSSource("UNIT_PRICE") AS numeric(9)) * CAST(DTSSource("SELL_RATE") AS numeric(9)) = DTSDestination("$ On Order")

Main = DTSTransformStat_OK

End Function

Steve Jones - SSC Editor SSC Guru Points: 738693 More actions · Answer 6

This isn't T-SQL code. It's VB Script, so you can't use CAST. You could use a VBscript function, such as CDBL

http://msdn.microsoft.com/en-us/library/3ca8tfek(VS.85).aspx

kamran_hassan_anjum SSC Rookie Points: 28 More actions · Answer 7

hi

thanks for your response.

please bear with me, i am new on this

i don't think so, I would be able to use that function in DTS packages.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 8

In a DTS package data transformation step, you are writing VBScript rather than T-SQL. You can't use CAST or CONVERT, there are separate functions such as CDbl(), CInt(), and CCur(). If all the fields are some numeric type (whether float, decimal, money, or integer types) you should be able to use them in an expression without explicit conversions. If you want to control the scale of the result, use the ROUND function on the whole expression. This example would return a value with a scale of two:

[font="Courier New"]DTSDestination("$ On Order") = ROUND(DTSSource("ORDER_QTY") * DTSSource("UNIT_PRICE") * DTSSource("SELL_RATE"), 2)[/font]

If you want to force the expression to use fixed-point arithmetic, you have to convert every field to currency:

[font="Courier New"]DTSDestination("$ On Order") = CCur(DTSSource("ORDER_QTY")) * CCur(DTSSource("UNIT_PRICE")) * CCur(DTSSource("SELL_RATE"))[/font]

To get better control of the precision and scale you should put the expression in the SQL statement for the data source:

[font="Courier New"]SELECT ..., CAST(ORDER_QTY * UNIT_PRICE * SELL_RATE AS numeric(9)) AS [$ On Order] FROM ...[/font]

Then the data transfomation step is a simple column copy. (Note: if any of the columns are FLOAT or REAL it might affect the result; they should be cast to numeric inside the expression.)

One last question is whether you realize that numeric(9) is equivalent to numeric(9,0), in other words the sample code you show is rounding everything to an integer.