# Convert decimal not working in case statement sql. RSS

## 3 replies

Last post Nov 09, 2016 03:02 PM by mardyDacasin

Member

90 Points

75 Posts

### Convert decimal not working in case statement sql.

Nov 09, 2016 01:14 PM|Omkar Mhaiskar|LINK

Hello All,

I am having select statement in sql as below

Select
(CASE
WHEN @CONVERTEDint=4 THEN   CONVERT(DECIMAL(9,4),@VALUE)
WHEN @CONVERTEDint=0 THEN  CONVERT(DECIMAL(9,0),@VALUE)
WHEN @CONVERTEDint=1 THEN  CONVERT(DECIMAL(9,1),@VALUE)
WHEN @CONVERTEDint=2 THEN  CONVERT(DECIMAL(9,2),@VALUE)
WHEN @CONVERTEDint=3 THEN  CONVERT(DECIMAL(9,3),@VALUE)
ELSE NULL --@VALUE
END) AS CONVERTEDVALUE

but when i execute query whatever CONVERTEDint value is (1,2,3,4) it will return result in decimal(9,4)

What is wrong in my case statement?

Omkar

Participant

1060 Points

346 Posts

### Re: Convert decimal not working in case statement sql.

Nov 09, 2016 02:33 PM|Sumit.Pokhriyal|LINK

Try below. It will solve your problem:

declare @VALUE float
declare @CONVERTEDint int
set @CONVERTEDint = 2
set @VALUE = '34.5656'
Select
(CASE
WHEN @CONVERTEDint=4 THEN  CONVERT(FLOAT, CONVERT(DECIMAL(9,4),@VALUE))
WHEN @CONVERTEDint=0 THEN  CONVERT(FLOAT, CONVERT(DECIMAL(9,0),@VALUE))
WHEN @CONVERTEDint=1 THEN  CONVERT(FLOAT, CONVERT(DECIMAL(9,1),@VALUE))
WHEN @CONVERTEDint=2 THEN  CONVERT(FLOAT, CONVERT(DECIMAL(9,2),@VALUE))
WHEN @CONVERTEDint=3 THEN  CONVERT(FLOAT, CONVERT(DECIMAL(9,3),@VALUE))
ELSE NULL --@VALUE
END) AS CONVERTEDVALUE

Thanks, Sumit.
/**** Please remember to "Mark as Answer" the responses that resolved your issue. ****/
• ### limno

All-Star

122188 Points

9682 Posts

Moderator

### Re: Convert decimal not working in case statement sql.

Nov 09, 2016 02:35 PM|limno|LINK

It can have only one data type and here is the implicit conversion to the highest.

In your case , it is decimal(9,4). If the datatypes in different condition cannot implicit convert, it will error out.

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm

Member

60 Points

25 Posts

### Re: Convert decimal not working in case statement sql.

Nov 09, 2016 03:02 PM|mardyDacasin|LINK

Hi,

reference:

http://stackoverflow.com/questions/11898427/case-then-clause-always-evaluated

http://www.fmsinc.com/free/newtips/sql/sqltip10.asp

"The first thoughts are generally one of the following "Since the first value evaluated is numeric, it is converted to decimal, and all other data is expected to be a decimal as well" OR "If SQL Server is able to convert ANY of the values to the specified type, then all values are expected to be of the converted type". However, that's not correct (although the second is close)!

The real problem is that if you choose to Convert the values anywhere within the Case statement, the datatype you are converting the values to is the expected type of ALL the values regardless of if they are of that type or not. Further, even if NONE of the values can actually be converted (even if the Convert line of code never executes), ALL of the values are still expected to be of the type specified by the Convert function! "

Dont forget to mark this as answer. Thanks

Regards,
Mardy