• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1328
  • Last Modified:

sql round to 2 decimal places

Hi,

I have the following line of code

case when a.[2008Data] > b.[2008Data] then 'Bear' + '  (' + convert(nvarchar(150),ABS((convert(Decimal(12,3),a.[2008Data])  - convert(Decimal(12,3),b.[2008Data]) )) * 100) +')' else 'Bull' +  '  (' + convert(nvarchar(150),ABS((convert(Decimal(12,3),a.[2008])  - convert(Decimal(12,3),b.[2008]) )) * 100)+')'  end as '2008',

but when i excute this i get 2.700
but I want to display 2.7 how can I do this please help

Thanks

R8VI
0
R8VI
Asked:
R8VI
1 Solution
 
Matt VCommented:
I would think changing the Decimal(12,3) to Decimal(12,1) should resolve that problem.  If you are just looking to truncate zeros, you would need to convert the value to a string and trim it I think.
0
 
Manuel Marienne-DuchêneITMCommented:
When you convert in Decimal(12,3) change to Decimal(12,1)
0
 
R8VIAuthor Commented:
if i change it to 12,1 results come out as 0.0
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Manuel Marienne-DuchêneITMCommented:
Have you tested Round(number,1)
0
 
R8VIAuthor Commented:
I tried that and got syntax error could you help with syntax please
0
 
Manuel Marienne-DuchêneITMCommented:
Before convert ex : ((Round(convert(Decimal(12,3),a.[2008]) ,1)
0
 
Scott PletcherSenior DBACommented:
I wouldn't reduce the precision of the calculation.  Instead, just do a final CAST of the full result (CAST will automatically round):


CAST (case when a.[2008Data] > b.[2008Data] then 'Bear' + '  (' + convert(nvarchar(150),ABS((convert(Decimal(12,3),a.[2008Data])  - convert(Decimal(12,3),b.[2008Data]) )) * 100) +')' else 'Bull' +  '  (' + convert(nvarchar(150),ABS((convert(Decimal(12,3),a.[2008])  - convert(Decimal(12,3),b.[2008]) )) * 100)+')'  end) AS decimal(10, 1) ) as '2008',
0
 
R8VIAuthor Commented:
Hi,

Thanks for all the suggestions I just need to remove any number after the 2 decimal places for example

2.700 should be displayed as 2.7

Please help

Thanks
r8vi
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now