• 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

    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/