September 1, 2010 at 3:01 pm
SELECT
CASE WHEN ROUND(18.00,2) = CONVERT(INT,18.00) THEN
CONVERT(INT,18.00)
ELSE
18.00
END
SELECT
CASE WHEN ROUND(18.01,2) = CONVERT(INT,18.01) THEN
CONVERT(INT,18.01)
ELSE
18.01
END
First stmt should print 18 and the second stmt should print 18.01
First stmt is printing 18.00 instead.
September 1, 2010 at 3:08 pm
A CASE statement can only return one datatype.
September 1, 2010 at 3:11 pm
Good catch Michael!
Try this:
SELECT
CASE WHEN ROUND(18.00,2) = CONVERT(INT,18.00) THEN
CONVERT(INT,18.00)
ELSE
18
END
SELECT
CASE WHEN ROUND(18.01,2) = CONVERT(INT,18.01) THEN
CONVERT(INT,18.01)
ELSE
18.01
END
Note the lack of decimals in the first CASE statement.
September 2, 2010 at 12:54 pm
Appreciate your help. Unfortunately for me, I cannot use hard coded values (18 like you mentioned). They come from the select query. I could achieve the intended result by moving this part into SET statements before SELECT, but I have more than one column in the SELECT query with the same requirement which further complicates the things. 🙂
September 2, 2010 at 2:16 pm
Can you provide a real example?
If a hard coded value is not an option, can you convert all possible CASE results to INT?
September 3, 2010 at 6:32 am
Might this be what you're looking for?
SELECT
CASE WHEN ROUND(18.00,2) = CONVERT(INT,18.00) THEN
convert(float,CONVERT(INT,18.00) )
ELSE
convert(float,18.00 )
END
SELECT
CASE WHEN ROUND(18.01,2) = CONVERT(INT,18.01) THEN
convert(float,CONVERT(INT,18.01))
ELSE
convert(float,18.01)
END
A case may not return one type for one clause, and another type for another clause. Converting to INT in the first clause of your original code will result in the value being truncated, and converted to INT type, but SQL will then convert it decimal to match the result of the second clause. The integer value 18 will be converted to decimal value 18.00, which is displayed as "18.00".
Converting to float will result in "18" being displayed, but be forewarned that the answer will no longer have the same value.
i.e. The decimal value of 18.01 is not equal to the float value 18.01, and if this result is used in further calculations, you could get different results.
September 3, 2010 at 7:27 am
As stated before, the issue is that a column (and therefore a case-statement) can only have one data type.
if you need a formatted print, you could convert the results into a VarChar.
SELECT
CASE WHEN ROUND(18.00,2) = CONVERT(INT,18.00) THEN
Cast(CONVERT(INT,18.00) as VarChar)
ELSE
Cast(18.00 as VarChar)
END
SELECT
CASE WHEN ROUND(18.01,2) = CONVERT(INT,18.01) THEN
Cast(CONVERT(INT,18.01) as VarChar)
ELSE
Cast(18.01 as VarChar)
END
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply