CODEWITHSUNDEEP
sql-serversql-server-2012
LCJ
LCJ

Reputation: 22662

SQL Server Datatypes for DB2/AS400 Decimals with byte length and Decimal Length

I have to load data from a AS400 DB2 system to SQL Server 2012. The extracted data is in a excel format from which I need to load to SQL Server. Referred IBM DB2/AS400 Subscribers

I have following decimal types in DB2/AS400.

  1. Byte Length = 2, Decimal Length =3, Dec Pos = 0
  2. Byte Length = 3, Decimal Length =5, Dec Pos = 2
  3. Byte Length = 3, Decimal Length =3, Dec Pos = 0
  4. Byte Length = 3, Decimal Length =5, Dec Pos = 4
  5. Byte Length = 4, Decimal Length =4, Dec Pos = 0
  6. Byte Length = 4, Decimal Length =6, Dec Pos = 3
  7. Byte Length = 4, Decimal Length =7, Dec Pos = 4
  8. Byte Length = 5, Decimal Length =5, Dec Pos = 0
  9. Byte Length = 5, Decimal Length =9, Dec Pos = 4
  10. Byte Length = 6, Decimal Length =11, Dec Pos = 0
  11. Byte Length = 6, Decimal Length =11, Dec Pos = 2
  12. Byte Length = 6, Decimal Length =11, Dec Pos = 4
  13. Byte Length = 6, Decimal Length =11, Dec Pos = 5
  14. Byte Length = 7, Decimal Length =13, Dec Pos = 4
  15. Byte Length = 7, Decimal Length =7, Dec Pos = 2
  16. Byte Length = 9, Decimal Length = 9, Dec Pos = 4

What is the corresponding datatype in SQL Server 2012 for each of these?

What is the rationale behind selecting these datatypes ?

Upvotes: 0

Views: 1586

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239734

In SQL Server, you don't get to define how much storage a decimal takes up - SQL Server has fixed storage requirements. So I think we have to ignore the Byte Length, if I'm interpreting that as a storage size requirement. As to the rest, in SQL Server, you declare decimal as:

decimal (p ,s)

Where p is the precision (total number of digits in all parts of the number), and s is the scale (number of digits to the right of the decimal point).

Since all of the types you've shown have a larger "decimal length" that "Dec Pos", I'm going to assume that these concepts map across directly to decimals precision and scale.

So type number 1 would be decimal(3,0) and number 16 would be decimal(9,4).

Upvotes: 1

Related Questions