October 20, 2016 at 1:47 pm
hi
i 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
October 20, 2016 at 1:57 pm
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
I am a bit confused here. Why are you formatting this number and then trying to stick it into an int column? Take that crazy formatting stuff out of here. It doesn't belong in the database. Formatting belongs on the client side. And not really sure why are you using numeric(10,0) here. Since you are putting it in an int column it would make sense to cast/convert it to an int.
select CAST(3454.6789 AS int)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 27, 2016 at 4:57 am
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply