I've been trying to solve this problem or just find an alternative to do this. I have filled a temp table with certain values for all columns except those columns that rely on calculations that need certain cell values from other columns in the temp table. I have a while statement that runs through each cell for one calculation column and this is the code that updates the fields on the calc column:
Code Block
EXEC('UPDATE #tblTemp
SET #tblTemp.['+@CalcColumns+'] = (CAST(ISNULL((SELECT #tblTemp.['+@Column1+']
FROM #tblTemp
WHERE #tblTemp.PK = '''+@PubKey+'''), 0.00)as money) - CAST(ISNULL((SELECT #tblTemp.['+@Column2+']
FROM #tblTemp
WHERE #tblTemp.PK = '''+@PubKey+''') , 0.00)as money))
WHERE #tblTemp.PK = '''+@PubKey+'''')
The problem is that it will not let me convert the two values to money. The error is:
Msg 235, Level 16, State 0, Line 1
Cannot convert a char value to money. The char value has incorrect syntax.
This is necessary to calculate the difference between these two cells from the temp table. I would appreciate any help! Thanks!
We have the same database in three different environment. The statement below works just fine in two database environment, but I am getting the error in the third one. All the databases are Microsoft SQL Server 2005. I could not pine point the source of the issue. Please any input that you might be able to provide. Thanks
The issue in simplest from
DECLARE @UDF_Value nvarchar(255)
set @UDF_Value = 111
SELECT CONVERT(money, @UDF_Value)
Msg 235, Level 16, State 0, Line 1
Cannot convert a char value to money. The char value has incorrect syntax.
I am trying to write a Procedure in SQL 2005 that retreives a list of rows from the database. I pass to the procedure sorting parameters. This procedure works fine for all of the fields that I enter in the Order By, except for one. The field that does not work is a varchar(500) field (named Description). I am not sure what the problem is. The error that I am getting is:
Msg 235, Level 16, State 0, Line 13
Cannot convert a char value to money. The char value has incorrect syntax.
Attached is the T-SQL Code. The declare and set statements in the beginning are for informational purposes. Any help is appreciated. ------------------------------------------------------------------------------------------------------ declare @Match varchar(75)
WHERE CONTAINS (ci.Keywords, @Match) AND ShowOnWeb=1
)
SELECT catitems.ID,
catitems.SupplierCode,
catitems.Description,
catitems.AUDIO_LINK,
catitems.HighCost,
catitems.Channels
FROM catitems
WHERE RowNo BETWEEN @StartPos AND @StartPos + @NumRows -1 ------------------------------------------------------------------------------------------------------
Msg 234, Level 16, State 2 There is insufficient result space to convert MONEY value to CHAR.
hi, I have one procedure that insert data into a table, I called the procedure 3 times ,and run it in one step, then got the above erorr message. what can I do to avoid this error message? and why I am getting it... thanks for your help regards
I want to print in an error message a money value but have to convert it to a varchar first. I do not have any clue how to do this. Could someone help me out?
SELECT @iError = @@error, @iRowCount = @@rowcount IF(@iError <> 0 OR @iRowCount <> 1) BEGIN CONVERT(@dValue, @cValue) set @cError = 'Error attempting to insert new record. Product : ' + @cProduct + ' Sub-account : ' + @cSubAccount + ' Cost : $' + @cValue RAISERROR(@cError, 16, 1) END
Ok i know this is simple but i just don't know the syntax. What I haveis a bunch of values and i want to be able to display them as moneyvalues with a $ in front.eg.160000001000160000TO THIS$16,000,000.00$1000.00$16,000.000I'm using MS SQL 2000Cheers
Hi Guys I need your help again, I am try to update several columns and the data type is 'money'. Below is the code I have used: UPDATE CAT_Products SET UnitCost ='10.00',UnitCost2 = '10.00',UnitCost3 = '10.00',UnitCost4 = '10.00',UnitCost5 = '10.00',UnitCost6 = '10.00' WHERE ProductCode = '0008' But it will not update, instead I get this error: ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- >[Error] Script lines: 1-9 -------------------------- Disallowed implicit conversion from data type varchar to data type money, table 'dbo.CAT_Products', column 'UnitCost'. Use the CONVERT function to run this query. More exceptions ... Disallowed implicit conversion from data type varchar to data type money, table '.dbo.CAT_Products', column 'UnitCost2'. Use the CONVERT function to run this query. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- The error message indicates that I need to use the convert function. But the columns data type is set at 'money' not 'varcher' . So do I need to convert data type to 'varcher' in order to update and convert back to data type 'money' when update complete? Or do I need to indicate in the update statement that data type is already 'money'? I am not sure how I would either. Thanks
Hi, I'm using the data type "money" in my SQL database and want to convert what's in txtPrice_textBox to the "money" format. I'm currently using the following code: ' objectCym.price = Convert.ToInt16(txtPrice_textBox.Text) ' Will this work? Is there any reason I should stay away from the "Money" data type?
I write using the SQL ODBC driver from software into a SLQ table called UPSSHIPMENT the format is as followed: JobNumber varchar 50 Weight real 4 FreightCost varchar 8 TrackingNumber varchar 50 Shipmethod varchar 50 VOIDID varchar 3
I then have a trigger set to update the PACKAGE table as followed CREATE TRIGGER [UPS] ON dbo.UPSSHIPMENT FOR INSERT
AS
BEGIN UPDATE PACKAGE SET WEIGHT = inserted.WEIGHT, FREIGHTCOST = inserted.FREIGHTCOST, TRACKINGNUMBER = inserted.TRACKINGNUMBER, COMMENTS = inserted.SHIPMETHOD FROM PACKAGE INNER JOIN inserted on PACKAGE.JOBNUMBER = inserted.JOBNUMBER WHERE inserted.VOIDID = 'N'
UPDATE PACKAGE SET WEIGHT = '', FREIGHTCOST = '0.00', TRACKINGNUMBER = '', COMMENTS = 'UPS VOID' FROM PACKAGE INNER JOIN inserted on PACKAGE.JOBNUMBER = inserted.JOBNUMBER WHERE inserted.VOIDID = 'Y'
END
The format of the PACKAGE table is as followed Jobnumber varchar 50 FreightCost money 8 TrackingNumber varchar 50 Comments varchar 2000 Weight real 4
I am getting the following error --------------------------- Microsoft SQL-DMO (ODBC SQLState: 42000) --------------------------- Error 260: Disallowed implicit conversion from data type varchar to data type money, table 'TESTing.dbo.Package', column 'FreightCost'. Use the CONVERT function to run this query. --------------------------- OK ---------------------------
How do you use the convert function to change the data before the update? Thank You! :mad:
I have a column type of float and How to convert it show like this
default value =39260.80 MY db use this ',' seperator instead of '.' wanna convert 39,260
This code is working if using '.' seperator
SELECTÂ parsename(convert(varchar,CAST(floor('39260.80') AS MONEY),1),2) not work using ',' seperator SELECTÂ parsename(convert(varchar,CAST(floor('39260,80') AS MONEY),1),2)
I need to always have a formatting of 999,999,999.00 in a column called PropertyMap9.
PropertyMap9 is always a nvarchar(max)
How do I SET a value using a CONVERT or CAST to accomplish this ?
Why does this give me a syntax error ? The PropertyMap7 set actually works and performs the calculation and sets the new value for that row, however the next line gives an error. I need all 3 columns PropertyMap9, 8, and 7 to always have the above formatting, while still maintaining the nvarchar(max) datatype in the column. HELP!
update PropertyMapValues
set PropertyMap7 = CONVERT(NVARCHAR(MAX),CAST(PropertyMap8 AS money) - (CAST(PropertyMap9 AS money)),1)
set PropertyMap9 = CONVERT(NVARCHAR(MAX),CAST(PropertyMap9 AS money))
END
Msg 102, Level 15, State 1, Procedure UpdatePropertyMap7, Line 59
I'm looking through some data in a third party application, trying to figure something out for a report. There is a char(1) column holding control characters that apparently effects the status of certain records, and whether they should appear on the report. I want to look at the integer value of those characters rather than the character representation, but I can't get it to work. I keep getting "Error converting data type varchar to numeric." I'm trying to do something like this: Code:
SELECT CONVERT(int, MyColumn) As IntValue FROM MyTable
I've tried every combination of cast and convert I can think of, as well as numeric data types other than int. There aren't that many distinct values, so I'm just going to do this manually, but I still want to know why it won't work, or what I'm doing wrong. Any thoughts?
I have a Column called PostNr which is of type Char(4) I want to be able to Convert it to int How can I accomplish that in query analyser, I tried changing it in Ent Manager, it keeps timing out. I data in Post is in this format, does not contain illegal character just number
Hi all,I have a column LateHours (Varchar). I want to put it in another table which has Latehours column in Decimal(4,2) Example = +04:33 Should be 4.33 (Only + values) Char to Decimal (4,2) Please Help me,Thanks,Janaka
Hi all, There are several columns called enabled with a char datatype in my database. One enabled column per table. These columns either have a value of T or F (true or false), depending on whether they're enabled or not. I want to change these columns to a bit datatype and insert the relevant value of true or false... I guess the best way to do this is to add a new column to a table with a bit datatype, and based on the value in the current enabled column, insert TRUE or FALSE. Anyone ideas on the best way to accomplish this? Thanks.
Hi, I have DB2 date value 00000000. If I'm exporting to SQL server using openquery that is automaticaly converting to char of 8 and stored as the same value 00000000. My question is how I can convert them as datetime value in SQL server 2000.?
declare @new table( id int not null identity(1,1), dat char(10)) insert into @new select'111214' union select'121214' select cast(dat as datetime)from @new -----------------------
Select Cast('100.1234' as float)give me the result 100.1234Now when I convert it back to char I want exactly 100.1234Select Convert(char(100),Cast('100.1234' as float))Gives me 100.123 (Here I was expecting 100.1234)When I doSelect STR(Cast('100.1234' as float),25,4)I get back the result as 100.1234However here I am not sure how many digits do I have after the decimalpoint. If I put some value likeSelect STR(Cast('100.1234' as float),25,8)I get 0's appended to it, which is again not desired.Thanks in advance,Jai
Hi, I am extracting data from DB2 into SQL server using DTS. We have the data type char(5) in DB2 and int in SQL server. I tried to give the select integer(columname) as columname from tablename in data source. I did parse the query it was Ok but If I tried to execute that giving the following error. Can you give me your answere for this issue.
--------------------------- Package Error --------------------------- Error Source : Microsoft OLE DB Provider for ODBC Drivers
Error Description : [StarQuest][StarSQL ODBC Driver][DB2]THE VALUE OF A CHARACTER STRING ARGUMENT WAS NOT ACCEPTABLE TO THE INTEGER FUNCTION
--------------------------- OK ---------------------------
Using SQL 2000 I have data in a sql table that is store in varchar like below
5.00 15.00 9.00
The integer part will never be bigger than 20. I need to move it to another SQL table that is char(5). I need the results that go in that table to like like below
05.00 15.00 09.00 I looked at the replace and cast but couldn't get the results. Any better approaches? Thanks
I read the topic from JROdden and this case is similiar but...
I got several varchar fields with values like 1.2 1.3 ... these I can covert with select CONVERT(dec(5,2), fieldname) as fieldname
In fact I also solved undefined- and NULL-values with. CONVERT(decimal(12, 2), CASE WHEN GESCHKOSTMAX IS NULL OR GESCHKOSTMAX < '0' THEN '0' ELSE GESCHKOSTMAX END) as GESCHKOSTMAX,
But now there are values like 1,4 and these ones neither CONVERT nor CAST will handle.
I tried the SELECT DISTINCT KMPAUSCHALE FROM extr_INTFIRMA WHERE (isnumeric(KMPAUSCHALE) = 1)
and get 0,40 0.25 0.30 and so on...
The error is: [Microsoft][ODBC SQL Driver][SQL Server]Error converting datatype varchar to decimal. (or float or numeric (whatever I tried))
I think the easiest way would be to insist on higher data quality but I also would like to solve this interesting challenge.
Thanks for any hints
By the way, I followed rudys link to http://rudy.ca/afdb.html and now I know how I could protect myself !!!!
There must be a voice in my head saying: Try the db-forum, try it and stay happy... ;-)
I have to store the result of a calculation in a column of type CHAR(7) (and am unable to change the column type).
The calculation can have results ranging in size from 0.1234567 to 99999999.
In the first case, I would need to store the value of 0.12345 in the column. In the later case, an error should be thrown.
So I need to store all of the significant digits from the left of the decimal (if there are < 7) and as many of the digits to the right as will fit into a CHAR(7), with the remaining precision being truncated.
