Datatype conversion

Question

Datatype conversion

khasim76

SSC Enthusiast

Points: 195
More actions
September 22, 2008 at 3:02 pm

#198634

Can somebody answer what datatype and precision this value should have. i am bit confused over this issue.In oracle its Number 22 and i am assigned to change datatypes in oracle to SQL server.
19214331247300.5

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

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

It fits into a numeric (15,1). Any numeric (or decimal, they're equivalent) with a precision of 15 or higher and a scale of 1 will work.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

khasim76 SSC Enthusiast Points: 195 More actions · Answer 2

could you please reason out . rather let my question be in this way

How you decide for number 22 , numeric (15,1) is suffiecient. my SSMA Migration tool went for numeric (4,1). Is there any logic or is it random based on numbers .Sorry for cross posting.Didnt knew sorry again.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Precision is the total number of digits in the number (to the left and right of the decimal place). Scale is the number of digits to the right of the decimal place.

The number you posted had 14 digits to the left of the decimal place and 1 to the right. That means that, at a minimum, it needs a precision of 15 (14+1) and a scale of 1.

I don't know what a Number(22) is on Oracle. I was judging just from what you posted. If there are larger numbers in that column, or numbers with more decimal places, then you'll need a different scale and precision

The largest number that can fit into numeric(4,1) is 999.9

select CAST('1000' as numeric (4,1))

Msg 8115, Level 16, State 8, Line 1

Arithmetic overflow error converting varchar to data type numeric.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

khasim76 SSC Enthusiast Points: 195 More actions · Answer 4

thanks i got an pretty much clearned from your posting.

Regards i really appreciate your effort

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

Pleasure.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 6

GilaMonster (9/22/2008)
Pleasure.

I'm sorry I missed it... Congrats on the 6k milestone, Gail. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 7

Jeff Moden (9/22/2008)
I'm sorry I missed it... Congrats on the 6k milestone, Gail. 🙂

😀 Thanks.

Working from home means I can keep a better eye on the place. :hehe:

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass