Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

convert decimal to int in case condition Expand / Collapse
Author
Message
Posted Tuesday, April 22, 2014 4:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 19, 2014 11:51 PM
Points: 62, Visits: 91
i have a table exam_setup
CREATE TABLE exam_setup
(
setup_id INT,
sub_id INT,
assignment decimal(4,1),
attendance decimal(4,1),
INT_1 decimal(4,1),
INT_2 decimal(4,1)
)
table values are,
sub_id = 75,
assignment = 25.0
attendance = 15.5
INT_1 =0.0
INT_2 =0.0

I have to fetch the values like
assignment = 25
attendance = 15.5
INT_1 =0
INT_2 =0
if decimal point is 0 then have to return integer value only.. but the below query doesn't work, why?

SELECT CASE WHEN assignment LIKE '%.0' THEN CONVERT(INT,assignment) ELSE assignment END,
CASE WHEN attendance LIKE '%.0' THEN CONVERT(INT,attendance) ELSE attendance END,
CASE WHEN INT_1 LIKE '%.0' THEN CONVERT(INT,INT_1) ELSE INT_1 END,
CASE WHEN INT_2 LIKE '%.0' THEN CONVERT(INT,INT_2) ELSE INT_2 END
FROM exam_setup
WHERE sub_id = 75

Post #1563747
Posted Tuesday, April 22, 2014 4:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 12,965, Visits: 10,738
CASE returns a single data type.
From BOL:

Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression.


Formatting is best done in the front tool, not in SQL Server itself.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1563755
Posted Tuesday, April 22, 2014 5:09 AM This worked for the OP Answer marked as solution
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 7:38 AM
Points: 749, Visits: 1,318
you can do this in t-sql using Format function.

Declare @tbl table ( id int, assignment decimal(11,8))

insert into @tbl
select 1, 10.00 union all
select 1, 8.55 union all
select 1, 0.55


select id, assignment, format(assignment,'###.##') AS assignment_format
from @tbl


As per BOL the return data type will be in nvachar. Koen Verbeeck is rightly said that

Formatting is best done in the front tool, not in SQL Server itself.


hope it helps
Post #1563770
Posted Tuesday, April 22, 2014 9:44 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 19, 2014 11:51 PM
Points: 62, Visits: 91
thank u
Post #1564080
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse