casting to Integer is not working properly?

execute these and check the result why is it so ?

declare @a decimal(8,3) =235.363
declare @b int          =1

select case @b
            when 1 then cast(@a as int)
            when 2 then CAST(@a as decimal(8,3))
end

Result  : 235.000

declare @a decimal(8,3) =235.363
declare @b int          =1

select case @b
            when 1 then cast(@a as int)
            --when 2 then CAST(@a as decimal(8,3))
end

Result  : 235

declare @a decimal(8,3) =235.363
declare @b int          =1

select case @b
            when 1 then cast(@a as tinyint)
            when 2 then CAST(@a as float)
end

Result : 235

Answers


What you see is not what you get.

For the column type, SQL Server picks the correct, more wide type (float over tinyint, decimal over int). You can verify that by doing select into instead of just select.

It's just the display rules that are different. When the selected column type is float, you don't see the trailing .000 when there is no fractional part. For decimal with explicit positions set, such as decimal(8,3), you will see the trailing .000 even if there's no fractional part. If you remove the specifier and only leave decimal as the column type, the .000 will disappear.

All that does not affect the actual column type, which is always the widest one.


This behaviour is documented in the BOL entry for CASE

Return Types

Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression. For more information, see Data Type Precedence (Transact-SQL).

If you follow the link to data type precedence you will see that float has higher precedence than decimal which in turn has higher precedence than tinyint so this behaviour is expected.


Probably cast operation will cast all the options to a bigger type.

From MSDN:

The data types of input_expression and each when_expression must be the same or must be an implicit conversion.

http://msdn.microsoft.com/en-us/library/ms181765.aspx


Casting to Integer is not working properly.

Your statement is not correct!

In CASE statement, you can only return one type of data, so according to your statement you can return either INT or decimal(8,3), since your case statement has decimal(8,3) so here INT data is implicitly converted to decimal! Please see below example:, always try to use same return type in CASE statement to get proper and expected result, thanks.

1.

select case @b
    when 1 then CAST(@a as int) -- return type INT
when 2 then CAST(@a as int) -- return type INT
end

2.

select case @b
     when 1 then CAST(@a as int) -- return type INT and then converted to decimal(8,3)
 when 2 then CAST(@a as decimal(8,3)) -- return type return type INT 
end

Need Your Help

Getting name(s) of FOLDER containing a specific SUBSTRING from the C:Temp directory in C#

c# asp.net file directory substring

Guys as the title says it I have to get the names of FOLDERS having a particular (user indicated) sub string.

How to render a composite component using a custom renderer?

jsf-2 composite-component renderer

I would like to know how to render a composite component, through Java,

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.