coool_sweet (10/20/2016)
hii have table from where data is coming like this
price
12.345
345.6789
23.56
now i am taking this into tem table nd what i need is remove decimal and round the number and then convert it to comma seprated number.
example if i have 3454.6789 i am using this to select CAST(3454.6789 AS DECIMAL(10,0)) which will be 3455
then i am using select format(3455, '#,#0') which will be 3,455
now i am getting this data from some table which has value stored as decimal
the table in which i am taking has this value
create table #temp1
(price int)
insert into #temp1
select format (CAST(3454.6789 AS DECIMAL(10,0)) ,'#,#0')) from table c
i am getting error Error converting data type nvarchar to numeric.
what should be my final data type here in temp table
Your target data type is INT (Integer) as specified in the (price int) part of your script. Anything you try to put in there must therefore be data type integer also. I concur with the other poster - you'd normally choose the format on output. However, if you absolutely have to store the value formatted, then you need to change the data type of the target column in your temporary table. This will work:
First specify your target column as type VARCHAR:
CREATE TABLE #temp1
(price VARCHAR)
Then use the following to produce your value with the thousand comma:
SELECT LEFT(CONVERT(VARCHAR, CAST(CAST(3454.6789 as int) AS MONEY),1),LEN(CAST(CAST(3454.6789 as int) AS MONEY))-2)
The LEFT.... -2 is assessing the length of the returned value (which will have decimals on it) and stripping the decimals
The CONVERT is turning the returned value into type VARCHAR so that it can be inserted into your target column
The CAST... AS MONEY produces the value with the thousand separator
The CAST... AS int produces your value minus the decimals
You will have to remember that this value will need to be converted to a number for calculation, insert or comparison purposes with other numeric data types.
Hope that's helpful.