CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008t-sql
user1820705
user1820705

Reputation: 681

Sql Server display decimals only if they are bigger than 0?

I have this select: select isnull(Pricea,0)-isnull(Priceb,0) as Differences The format of the columns is decimal(12,4). My question is: I could somehow to return decimals only if they are bigger than 0? It seems to be confusing if the result will be for e.g 4.0000 so I would want to display the decimals only if they are bigger than 0. Is this possible?

Upvotes: 0

Views: 4523

Answers (2)

Teddy
Teddy

Reputation: 1417

When ceiling(Num) = floor(Num), the number is a integer

select case when ceiling(Num) = floor(Num) 
       then      CONVERT(varchar, CAST(Num as decimal))
       else      CONVERT(varchar, Num) 
       end

Upvotes: 2

bummi
bummi

Reputation: 27377

It's just the kind of beeing displayed in MangementStudio for the Datatypes. For just adapting the display kind you could do something like

Declare @a table  (a decimal(12,4),b decimal(12,4))
insert into @a Values(12.45,10.45)
insert into @a Values(12.45,10.4512)
insert into @a Values(12.4512,10.4500)

Select Cast(Case when a-b<>Floor(a-b) then Cast(a-b as float) else  a-b end as Varchar(30)) as Diff
from @a

Upvotes: 1

Related Questions