Rounding off numbers not to display any decimals

I imported data from excel into SQL server  and the numbers were rounded off in Excel. After importing, the numbers displayed in 2 decimal places. How will I rounded of the numbers back so the sql output is similar to its original excel values
zachvaldezAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russ SuterCommented:
It sounds as though your SQL Server column has a scale of 2 but we don't know the data type. We also don't know the precision of that number or the precision or scale of the number in Excel so it's a little hard to follow what you're asking. That being said, rounding is a lossy process. Meaning that once you round you lose data. 2.376 rounded to two decimal places is 2.38 but that same number could have come from any value between 2.375 and 2.384. There's no way of knowing and no way to "round back". Can you clarify your question, please?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
It might have with the column data type in SQL Server. What are you using?
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AlanConsultantCommented:
Hi,

It sounds like maybe in Excel you have not actually rounded the numbers, but just displayed them to the nearest integer (so that Excel still contains the full decimal values).

If you actually round them in Excel (using the ROUND function) first, then what gets exported / imported to SQL will be the actual integer values.


Alan.
1
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

zachvaldezAuthor Commented:
I just imported the file and no idea how it was formatted.
0
Russ SuterCommented:
In order to accurately predict output you need to know your input. Nobody else has any idea how it was formatted either. Can you go back to the original file and look at it?
1
AlanConsultantCommented:
Hi,

I suggest you make a copy of the source data file in Excel (just to be safe), find a number that comes through to SQL 'incorrectly', and look at what you see on screen, then compare that to what you see if you click on the cell containing that number, and what is in the formula bar.

If the cell contains a formula itself (rather than just a hard coded number), then select the entire formula in the formula bar, and press F9.  That will 'evaluate' the formula and show the actual value that Excel has in that cell.

If the number displayed on the worksheet is different than what Excel actually has in the cell, then the most likely reason is that someone has formatted the cell to display, say, only integers, rather than the actual result.

When you export from Excel, you will get the actual value, not the formatted value.

Feel free to post a sample here (if you can), and we can check that for you.


Another option would be to turn on the 'precision as displayed' option in Excel.  I don't like that option as it can sometimes cause problems, but it might fix your problem in one go:

https://support.office.com/en-us/article/set-rounding-precision-e5d707e3-07a8-4df2-810c-218c531eb06a

Alan.
0
zachvaldezAuthor Commented:
I found out that when you import and excel to sql, the data types  were float. It could be the reason, why there were decimal places.
I renamed the data types to integer and found out it fixed the problem.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
That's what I told you in my first comment:
It might have with the column data type in SQL Server. What are you using?
0
AlanConsultantCommented:
Solved
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.