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)
hi,
i'm get some strange behaviour with the money and float type. with a calculator divide 70 by 47 and multiply by 40 (70 / 47 * 40) and you get 59.5744 (rounded to nearest 4 decimal places). now try this in query analyser with the 70 as a money type and the result as a float:
declare @price money
declare @result float
select @price = 70
select @result = @price / 47 * 40
Print 'result = ' + str(@result,18,4)
now this will print out 59.5720
am i missing something ?
I can't take full credit for this. I want to share this with Jeff Moden who did the important research for this calculation here.
All I did was just adapting some old code according to the mantissa finding Jeff made and optimized it a little
Some test codeDECLARE@SomeNumber FLOAT,
@BinFloat BINARY(8)
SELECT@SomeNumber = -185.6125,
@BinFloat = CAST(@SomeNumber AS BINARY(8))
SELECT@SomeNumber AS [Original],
CAST(@SomeNumber AS BINARY(8)) AS [Binary],
dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Converted],
@SomeNumber - dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Error]
And here is the code for the function.CREATE FUNCTION dbo.fnBinaryFloat2Float
(
@BinaryFloat BINARY(8)
)
RETURNS FLOAT
AS
BEGIN
DECLARE@Part TINYINT,
@PartValue TINYINT,
@Mask TINYINT,
@Mantissa FLOAT,
@Exponent SMALLINT,
@Bit TINYINT,
@Ln2 FLOAT,
@BigValue BIGINT
SELECT@Part = 1,
@Mantissa = 1,
@Bit = 1,
@Ln2 = LOG(2),
@BigValue = CAST(@BinaryFloat AS BIGINT),
@Exponent = (@BigValue & 0x7ff0000000000000) / EXP(52 * @Ln2)
WHILE @Part <= 8
BEGIN
SELECT@Part = @Part + 1,
@PartValue = CAST(SUBSTRING(@BinaryFloat, @Part, 1) AS TINYINT),
@Mask =CASE WHEN @Part = 2 THEN 8 ELSE 128 END
WHILE @Mask > 0
BEGIN
IF @PartValue & @Mask > 0
SET @Mantissa = @Mantissa + EXP(-@Bit * @Ln2)
SELECT@Bit = @Bit + 1,
@Mask = @Mask / 2
END
END
RETURNSIGN(@BigValue) * @Mantissa * POWER(CAST(2 AS FLOAT), @Exponent - 1023)
END
Thanks again Jeff!
Peter Larsson
Helsingborg, Sweden
How do I format the money or float field types to 2 decimal places during a SELECT statement?
View 4 Replies View RelatedWhat is the difference between Money and (Float or Decimal) Datatype. If we use Float or Decimal instead of Money, will we loose any functions..?
View 4 Replies View RelatedHappy Friday!
A while since I have posted a question, and this one is probably real easy.
I am trying to store numeric values from a php form in MSSQL 2000 database. However, the columns are set to float and if the value is 1.00, when entered into the table it is saved as 1
If I change the column type to money, the query fails, with an error message of conversion of datatype varchar to datatype money statement terminated.
anybody know what I need to do? do I need to do something in my query to specify that this is NOT varchar data?
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
@cValue is a varchar(20)
@dValue is money
Any help?
Mike B
Hi i've a staging table were i have datatypes of nchar.
I'd like to convert these to money datatype
Am trying to do this like this but the data return is not correct there is aslo null and blank rows so i must account for them also.
select cast(IsNumeric((Value2)) as money)
fromtbl_Sales
cast(round([YearlyProfit]-[MonthlyProfit],0)as int),
cast(round(isnull(sum(SalesProfit]),0),0) as int
How can I convert these columns to money so that I can display the result with comma included.
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
View 1 Replies View Related
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'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!
Hi i've a staging table were i have datatypes of nchar.
I'd like to convert these to money datatype
Am trying to do this like this but the data return is not correct there is aslo null and blank rows so i must account for them also.
select cast(IsNumeric((Value2)) as money)
from tbl_Sales
Hi,
why does converting integer to float take so long? Its a column with about 5 Million rows.
I want to avoid cast(inumber1 as float) / cast(inmuber2 as float), thats why converting them. Queries should be a bit faster after that.. hope so :)
Thanks a lot
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?
Thanks,
David
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:
hi, how can I convert a float to a char so I can do:
'£' + convert(char,amount) AS [money]
When I do the above the number is like 1.07848e+006
rather than 1078480
thans for any help.
jamie
I have a sp that receive a rango of float values,and I need to convert this values to a varchar(20).
I am trying the next but I got a strange result.
@intNoTarjIni = 121456790
set @strTarjeta=cast(@intNoTarjIni as varchar(20))
returns: 1.21457e+008
set @strTarjeta=convert( varchar(20),@intNoTarjIni)
returns:1.21457e+008
How can I convert sucessfully a float to varchar?
Hi -
My field TDMergerVotePercent is defined as a float field. I want it to return 'NA' when the value is -1 but I'm getting the error message
'Error converting data type varchar to float' in my aspx page. Is there where the CAST function can be used? Thanks
CASE
WHEN TDMergerVotePercent = -1 THEN 'NA'
WHEN TDMergerVotePercent IS NULL THEN ''
ELSE TDMergerVotePercent
END AS TDMergerVotePercent
I have a data type float with a value of 10000487930 that I'm trying to insert into a data type nvarchar and am getting the result of '1.00005e+010'. I've tried cast(field as nvarchar) however this is not working. What might fix this? I cannot change the insert table data type.
View 3 Replies View RelatedHello,I would like to convert a field from ntext field found in one databasetable to float field found in another database table. The reason why Iwant to do this is a long one.I have tried the following and playing around with the following:declare @valuePointer varbinary(16)<Row cursor logic to initialize @valuePointer to be a pointer to thesource ntext field>update TargetFloatTable set TargetFloatTable.TargetFloatValue =CAST(CAST(@valuePointer AS nvarchar) AS float)where TargetFloatTable.Id = @Idbut is not working for me.Hoping someone out there can help.Thanks,Cally
View 5 Replies View RelatedSelect 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
View 4 Replies View RelatedIs this possible? I'm trying to user a lookup task and the data I want to compare is a varchar to float. How can I do this? I tried using the data conversion task and it didn't work and also tried cast and convert. Is this even possible or is there a way around it?
thanks,
I have the below Ms.Access code that I would to transition into SQL.
Is CCUR a usable function in SQL, or would I have to use the convert to money function?
Charges: Sum(((CCUR([Fee Schedule Rate])*CCUR([Units_Charged]))))
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
Incorrect syntax near '='.
I've three columns:
Length Width Height
1.5 2.5 10
2 3.7 19
in Query I want to display Like 1.5 X 2.5 X 10 (Length, Width, Height).....
???
I'm running the following statement...
The column is currently float (8). Need to convert to numeric. I've tried cast, convert. no go for either.
Any help on this would be greatly appreciated.
..... select ... convert(numeric(38,0), colname)
TIA,
Jeremy
Hello,
Is there a way to convert varchar or char to float?
Thank you
I have a field in my database that is stored as varchar. The values are usually contain a decimal, and should have really been a float or decimal. In order for me to do analytics in my BI environment, I need to convert this to a float or decimal.
eg of values.
10.00
20.00
0.00
15.00
or could be missing when I use cast(value as float) or cast(value as decimal(9,2)) or convert(float, value) I get an error
Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to numeric.
Hi,
Can you guys help me out?
I m trying to sum up some varchar-typed field. I need to convert it to float before doing the summing up so I m using "Cast".
I do get the answer but its not the correct figure. My SQL statement is as follow:
SELECT Sum((Cast(Qty1 as float)) + (Cast(Qty2 as float))) as intAnswer FROM TableName
Please help.
What actually happens when I convert date to float? Select convert(float,getdate(),112)
View 1 Replies View RelatedI have the following CASE statement:
SELECT CASE WHEN [TBL_whole_rock_geochem].au = 0 THEN '<' + [TBL_ActLab_codes].[Au] ELSE [TBL_whole_rock_geochem].[Au] END AS [Au det]
It errors on the highlighted code.
Error converting data type nvarchar to float
the code should return :
<0.5
<1
I have tried '<' + ISNUMERIC([TBL_ActLab_codes].[Au]) but received this error
Conversion failed when converting the varchar value '<' to data type int.
How I can concatenate the < with a float value?