How do I force FLOAT / VARCHAR values to be INTs in an insert statement?
-
This is a request for someone to help prevent me getting the error message:
Syntax error converting the varchar value '1458.00' to a column of data type int.
-
If you will only be dealing with Whole numbers and all of the numbers would be like your example. You'd just need to drop the .00. This can be done via numerous means. Substring() OR Round(), Along with, Cast or Convert should work well. You'd need to test to see what works besrt for your data. However, if you will ever have soemthing where you have a decimal value of say '1458.50' you can't store this as an int. No decimal places are allowed in a int, you'll need to store it as a Decimal.
Alternatively, however you App dev team will probably hate it, you could multiply everything by 100 going into your DB and devide by 100 when displaying everything. This will remove the Decimal and leave you with a whole number that can be stored as an int. I strongly recommend against this, only because I've worked with systems liek this and it's a pain trying to remember at what state you have the data and if you need to divide/Multiply etc and how that will change your results.
-Luke.
-
I just want to remove the '.00' from every value in a collumn. Can that be done with Substring. The problem is the number of digits before '.00' is varied. I can't use Round() because I am dealing with a VARCHAR. Could I combine CAST() and ROUND() to produce a single function?
-
This will only work as long as all of your values end with .00.
Create TABLE #tmp(
col1 varchar(10) NOT null
)
INSERT INTO #tmp (COL1) vALUES(1000.00)
INSERT INTO #tmp (COL1) vALUES(500.00)
INSERT INTO #tmp (COL1) vALUES(99900.00)
select col1 AS MyVarchar,
cast(COL1 as DECIMAL) AS Mydec,
cONVERT(INT,cast(COL1 as DECIMAL)) AS Myint
from #TMP
drop TABLE #TMP
MyVarchar Mydec Myint
---------- -------------------- -----------
1000.00 1000 1000
500.00 500 500
99900.00 99900 99900
You can't cast or convert a varchar to an int while there is a decimal portion, but if you cast a whole number with a decimal to a decimal first AS above you then be able to cast it as an int.
-
try this
declare @vchar vchar(20)
set @vchar = '1458.00'
select cast(substring(@vchar, 1, patindex('%.%', @vchar) - 1) as int)
hth
-
No need to enter the dark and slow realm of coversions to characters...
DECLARE @vChar VARCHAR(20)
SET @vChar = '1458.00'
SELECT CAST(CAST(@vChar AS MONEY) AS INT)
--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)
Intro to Tally Tables and Functions -
If you have @vChar = '1458.87' and you are trying to treat it this way you'll perfectly match it to integer value 1458. But they are actually not equal and not suppose to be matched.
I'm pretty sure FLOAT values are not the issue. Server will implicitly convert it into INT without any problem.
So, the non numeric values are the issue. And people use to convert INT values to VARCHAR, not VARCHAR to INT.
Like this:
WHERE YourColumn = CONVERT(VARCHAR(10), @YourVar)
Of course, it will not match 10 to 10.00, so you need to include required formatting into CONVERT function.
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply