CODEWITHSUNDEEP
sqlsql-serverdatabase
Abdullah Al Mamun
Abdullah Al Mamun

Reputation: 392

Custom conversion of decimal value in sql

I have to customize a specific column value of a query. where I have four types of cases like

  empcode AbsentDays
  1       0.00
  6       0.50
  2       1.00
  3       1.50
  4       2.00
  5       2.50

and my expected output would be like

  1       0
  6       0.5
  2       1
  3       1.5
  4       2
  5       2.5

To do this I have tried this written below

AbsentDays = case 
                when AbsentDays = 0.00 
                   then convert(int, AbsentDays) 
                when AbsentDays >= 1.00 
                   then convert(decimal(10, 1), AbsentDays)
                -- when AbsentDays = 0.50 
                --    then 0.5
            end

But using this I can't get my desired result it is overriding other values.

Upvotes: 3

Views: 164

Answers (3)

George Menoutis
George Menoutis

Reputation: 7240

A column can only have a single datatype. In order to see the different cases in the ways you describe, you'll have to use a varchar/nvarchar datatype:

AbsentDays = case 
                when AbsentDays = floor(AbsentDays)
                   then convert(nvarchar(max),convert(int, AbsentDays)) -- change to int if no decimal part
                else convert(nvarchar(max),convert(decimal(10, 1), AbsentDays)) -- else return one decimal
              end

Upvotes: 2

Amir Molaei
Amir Molaei

Reputation: 3820

You can do it by casting it to float because the rightmost zeros after decimal point are trimmed in float: 

select empcode, cast(AbsentDays as float)
from tbl

Upvotes: 0

Arnaud Peralta
Arnaud Peralta

Reputation: 1305

Don't cast as a float, you will lose in precision.

SELECT FORMAT(CAST(2.0 AS DECIMAL(9,6)), 'g15') -- will print 2
SELECT FORMAT(CAST(0.50 AS DECIMAL(9,6)), 'g15') -- will print 0.5

Upvotes: 1

Related Questions