• coool_sweet (10/20/2016)


    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

    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.