Decimal(65,2) in MySQl to Sql server Data Type

Question

Decimal(65,2) in MySQl to Sql server Data Type

Mr. Kapsicum

SSCertifiable

Points: 6128
More actions
May 29, 2014 at 12:15 pm

#289315

Hi,
I need the exact Data Type Mapping for DECIMAL(65,2) MySQL to SQL SERVER.
What data type should be used in SQL Server for this
Thanks in Advance.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Mr. Kapsicum (5/29/2014)
Hi,
I need the exact Data Type Mapping for DECIMAL(65,2) MySQL to SQL SERVER.
What data type should be used in SQL Server for this
Thanks in Advance.

How big are these numbers? (65, 2) suggests an AWFULLY big number.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sgmunson SSC Guru Points: 110640 More actions · Answer 2

SQL Server 2008 only supports up to 38 decimal digits, so I can't imagine there's any practical way to support 65. However, if you know that the values being stored don't exceed some known number of digits, then you might be able to CAST something to a more practical format on the MySQL side prior to any data transfer.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 3

Hi,

This Data Type is of the Source Table. We Insert Data from Mysql DB to SQL Server.

The Data Type for few columns in Source Table is Decimal(65,2), so i need data type mapping in Sql server for this

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Mr. Kapsicum (5/29/2014)
Hi,
This Data Type is of the Source Table. We Insert Data from Mysql DB to SQL Server.
The Data Type for few columns in Source Table is Decimal(65,2), so i need data type mapping in Sql server for this

We know you are trying to import data. The problem is there is a limitation on decimal in sql server. I can't comprehend any type of situation where you would need that large of a number. I suppose if you were counting the number of grains of sand on a beach or maybe the surface of the planet in square inches. Is there not a smaller size you could use here? Do you really have numbers that large in your data?

Actually just for grins I used a little googlefu and the surface area of earth in square inches is nowhere near 65 significant digits. Assuming the total square miles of earth is about 197 million and 1 square mile is 4,014,489,600 square inches we come up with 790,854,451,200,000,000 square inches. This is only 18 significant digits. You are talking astronomically larger scale to get to 65 significant digits.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sgmunson SSC Guru Points: 110640 More actions · Answer 5

There is no direct mapping for a field defined as DECIMAL(65,2). As I indicated previously, there's just no way for SQL Server 2008 to support 65 decimal digits - it only supports 38. So you are going to HAVE TO LOOK AT YOUR DATA and find out whether or not the 65, 2 designation is necessary or not, and go from there.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Matt Crowley SSCarpal Tunnel Points: 4163 More actions · Answer 6

I am compelled to post this link here:

XKCD: What if[/url]

sgmunson SSC Guru Points: 110640 More actions · Answer 7

Matt Crowley (5/29/2014)
I am compelled to post this link here:
XKCD: What if[/url]

Yes, you were... We would have had to find you and beat you if you didn't.... 😉 LOL... just kidding...

and thanks for that article... Just the kind of reasoning I like to use...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

Matt Crowley (5/29/2014)
I am compelled to post this link here:
XKCD: What if[/url]

That is awesome. Hadn't seen that one before.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 9

Mr. Kapsicum (5/29/2014)
Hi,
This Data Type is of the Source Table. We Insert Data from Mysql DB to SQL Server.
The Data Type for few columns in Source Table is Decimal(65,2), so i need data type mapping in Sql server for this

I have to ask, what's in this column that needs 67 digits of precision down to 2 decimal places?

--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)

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

Jeff Moden (5/29/2014)
Mr. Kapsicum (5/29/2014)
Hi,
This Data Type is of the Source Table. We Insert Data from Mysql DB to SQL Server.
The Data Type for few columns in Source Table is Decimal(65,2), so i need data type mapping in Sql server for this
I have to ask, what's in this column that needs 67 digits of precision down to 2 decimal places?

12345678901234567890123456789012345678901234567890123456789012345.99

Or the much easier read formatted version.

12,345,678,901,234,567,890,123,456,789,012,345,678,901,234,567,890,123,456,789,012,345.99

All kidding aside, the point many of us are making is that it seems highly unlikely you need a decimal that holds this much. You could probably get by with a much more reasonable size that will hold your data.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/