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

# 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

Thanks

R8VI
0
R8VI
1 Solution

Commented:
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

ITMCommented:
When you convert in Decimal(12,3) change to Decimal(12,1)
0

Author Commented:
if i change it to 12,1 results come out as 0.0
0

ITMCommented:
Have you tested Round(number,1)
0

Author Commented:
I tried that and got syntax error could you help with syntax please
0

ITMCommented:
Before convert ex : ((Round(convert(Decimal(12,3),a.[2008]) ,1)
0

Senior 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

Author 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

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.