convert decimal to int in case condition

  • 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

  • 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

  • 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

  • thank u

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply