April 22, 2014 at 4:20 am
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
April 22, 2014 at 4:33 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 22, 2014 at 5:09 am
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
April 22, 2014 at 9:44 pm
thank u
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply