Skip to content
Advertisement

Conditionally convert a decimal value to integer

create table #Temp
(
    id int,
    Volume decimal(18,3)
)

insert into #Temp(id,Volume)values(1,10)
insert into #Temp(id,Volume)values(2,10.2)


id  Volume
-----------------
1   10.000
2   10.200


Declare @Type as int
set @Type=1


 select Id,Convert(varchar(10),CASE WHEN @Type=1 THEN CAST(Volume AS INT)
 ELSE Volume  END) AS Quantity from #Temp

It is showing a result like this

id  Volume
-----------------
1   10.000
2   10.000

But I want result like this when type is 1 then result should be in integer format:

id  Volume
-----------------
1   10
2   10

on else condition (@Type rather than 1) I want result like this

    id  Volume
    -----------------
    1   10.000
    2   10.200

I have tried this query

select Id,Convert(varchar(10),CASE WHEN @Type=1 THEN CAST(Volume AS INT) 
ELSE Volume  END) AS Quantity from #Temp

Advertisement

Answer

This will work for you.

 select Id, (CASE WHEN @Type=1 THEN convert(varchar(10),convert(int,volume))
 ELSE convert(varchar(10),Volume)  END) AS Quantity from #Temp

When @type is 1, the result will be 10 for both values. Otherwise it will be exact value in the table.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement