January 7, 2005 at 1:40 pm
Hi,
I want to convert a varchar datatype to int. The varchar column actually contains decimal values. for example
varchar Column
-------------
36.00
20.00
35.00
60.00
....
...
I want it to be converted to
Int Column
----------
36
20
35
60
....
...
right now I was doing like this
cast((round(cast(qty as float),0)) as int). Is this a better way? If not please let me know the better way.
Thanks,
Sridhar!!
January 7, 2005 at 2:15 pm
I don't see any problems with your code. You "may" not need round, if in fact your data just shows zeros in the decimal places. But otherwise it looks fine.
I have always used CONVERT. Is there an advantage to using CAST over CONVERT?
I wasn't born stupid - I had to study.
January 8, 2005 at 1:14 pm
As Farrell suggested, you don't need to do the rounding thing because INT will do the rounding for you.
Either of these will work...
SELECT CONVERT(INT,CONVERT(MONEY,'27.49')) SELECT CAST(CAST('26.50' AS MONEY) AS INT)
Obviously, you would substitute the column name for the string literal in both of the above statements.
Just a couple of side notes...
Although an intermediate conversion to float will certainly work in this case, you should be advised that FLOAT does it's calculations based on Binary rather than Decimal numbering and the results will not always be what you would expect.
INT will round up at the .50 mark and round down at the .49 mark.
In case anyone else reads this, the reason two conversions are necessary is because the decimal point causes the following error when trying to convert a VARCHAR representation of a number with a decimal point in it, directly to INT.
SELECT CONVERT(INT,'27.49')
Server: Msg 245, Level 16, State 1, Line 1 Syntax error converting the varchar value '27.49' to a column of data type int.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 10, 2005 at 2:37 am
Is there an advantage to using CAST over CONVERT?
CAST is the ANSI way of doing things. IIRC, even MS recommends this over CONVERT. Though only CONVERT allows for the style parameter for DATETIMES. I guess this is a case for the inconsequent category
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
January 10, 2005 at 7:31 am
Or
SELECT CAST(FLOOR(Qty) AS int)
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply