• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1286
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

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