Skip to content
Advertisement

how to resolve data type incompatability issue in SQL server

I have been trying to build below logic

Declare @OT float 
Declare @Remark varchar(100)
set @OT = 9.52
set @Remark = '1-25 hours'
select case when @Remark='1-25 hours'  then  (FLOOR((@OT * 100)/100)*150 + (((@OT * 100) % 100.00) * (150/60))) --@OT*150
                   when @Remark='26-35 hours' then (25*150) + ((FLOOR((@OT-25.00) * 100)/100 )*200 + (((@OT-25.00) * 100)%100) * (200/60))  --((@OT-25.00)* 200)
                   when @Remark='36-45 hours' then (25*150) + (10*200) + ( FLOOR(((@OT-35.00)*100)/100)*250 + (((@OT-35.00) * 100)%100) * (250/60)) --((@OT-35.00) * 250)
                   when @Remark='46-55 hours' then (25*150) + (10*200) + (10* 250) + (FLOOR(((@OT-45.00) *100)/100)*350 + (((@OT-45.00) * 100)%100) * (350/60)) --((@OT-45.00) * 350)
                   when @Remark='56-65 hours' then (25*150) + (10*200) + (10* 250) + (10 * 350) + (FLOOR(((@OT-55.00)*100)/100)*550 + (((@OT-55.00) * 100)%100) * (550/60)) --((@OT-55.00) * 550)
                   when @Remark='aboce 65 hours' then (25*150) + (10*200) + (10* 250) + (10 * 350) + (10 * 550) + (FLOOR(((@OT-65.00)*100)/100)*650 + (((@OT-65.00) * 100)%100) * (650/60)) --((@OT-65.00)* 650)
             end

But i am getting below error

Msg 402, Level 16, State 1, Line 19 The data types float and numeric are incompatible in the modulo operator.

Advertisement

Answer

Just cast to INT before performing the modulo:

Declare @OT float 
Declare @Remark varchar(100)
set @OT = 9.52
set @Remark = '1-25 hours'

SELECT FLOOR((@OT * 100)/100)*150 + ((CAST(@OT * 100 AS INT) % 100.00) * (150/60))

Here is the complete code:

select case when @Remark='1-25 hours'  then  (FLOOR((@OT * 100)/100)*150 + ((CAST(@OT * 100 AS INT) % 100.00) * (150/60))) --@OT*150
                   when @Remark='26-35 hours' then (25*150) + ((FLOOR((@OT-25.00) * 100)/100 )*200 + (CAST((@OT-25.00) * 100 AS INT)%100) * (200/60))  --((@OT-25.00)* 200)
                   when @Remark='36-45 hours' then (25*150) + (10*200) + ( FLOOR(((@OT-35.00)*100)/100)*250 + (CAST((@OT-35.00) * 100 AS INT)%100) * (250/60)) --((@OT-35.00) * 250)
                   when @Remark='46-55 hours' then (25*150) + (10*200) + (10* 250) + (FLOOR(((@OT-45.00) *100)/100)*350 + (CAST((@OT-45.00) * 100 AS INT)%100) * (350/60)) --((@OT-45.00) * 350)
                   when @Remark='56-65 hours' then (25*150) + (10*200) + (10* 250) + (10 * 350) + (FLOOR(((@OT-55.00)*100)/100)*550 + (CAST((@OT-55.00) * 100 AS INT)%100) * (550/60)) --((@OT-55.00) * 550)
                   when @Remark='aboce 65 hours' then (25*150) + (10*200) + (10* 250) + (10 * 350) + (10 * 550) + (FLOOR(((@OT-65.00)*100)/100)*650 + (CAST((@OT-65.00) * 100 AS INT)%100) * (650/60)) --((@OT-65.00)* 650)
             end

The issue is you are not allowed to perform modulo, because:

dividend must be a valid expression of any one of the data types in the integer and monetary data type categories, or the numeric data type.

and even you multiply with 100, you are not changing the type.

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement