converting varChar with decimal places in to int

Hi, I'm having trouble converting a table that currently has data stored in a varchar format to int.

It works ok with most numbers but doesn't seem to work when there is a decimal point in them.

604152.7 fails
604152 works

Can someone please advise my best way round this please. I'm using SQL 2012 if that makes a difference and need these in int format for use in another program.

Thanks, Neil
LVL 3
Neil ThompsonSenior Systems DeveloperAsked:
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.

NorieVBA ExpertCommented:
Neil

What would you want to happen when there is a decimal point?
0
Neil ThompsonSenior Systems DeveloperAuthor Commented:
Apologies, I've just asked

We only need the 6 numbers before the .
0
Bill PrewCommented:
Try:

CAST(ROUND(ColName,0,1) AS INT)


»bp
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Neil ThompsonSenior Systems DeveloperAuthor Commented:
That's great many thanks, how would I now go about updating all these in the table to remove the unneeded bits (rather than having to cast etc each time)

I have a field sID that is the primary key
0
Bill PrewCommented:
So there is an existing column that is varchar and has this text in it?  And you have a new column that is integer and you want to place the converted number in that?

Or are you wanting to update the existing varchar column just removing the decimal part of the "number"?

Or, if you are wanting something different please explain in more detail.


»bp
0
Neil ThompsonSenior Systems DeveloperAuthor Commented:
Hi Bill

I guess ideally just remove the decimal part from the current columns sEasting and sNorthing as they are currently varChar. I assume after that I could simply amend them to int

Regards
Neil
0
Bill PrewCommented:
I *think* this should work, give it a try in a test environment.  I believe SQL Server should take care of the type conversions...

UPDATE table1 SET sEasting = ROUND(sEasting,0,1), sNorthing = ROUND(sNorthing,0,1);


»bp
0

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
Neil ThompsonSenior Systems DeveloperAuthor Commented:
perfect, many thanks
0
awking00Commented:
Just out of curiosity, could you try to alter those columns to int? If it completes, I suspect the data might be truncated as well so no update would be necessary.
For example, alter table table1 alter column (sEasting numeric 18,0).
0
Neil ThompsonSenior Systems DeveloperAuthor Commented:
Thanks awking00 and good point.

I must admit I never tried this as I initially tried to amend the column itself straight from varChar to int and it was failing on the decimal numbers but hopefully that may be useful to anyone looking at this issue in the futire
0
awking00Commented:
Were you trying to "amend" the column with an update or an alter table statement?
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.