SQL Operand data type real is invalid for modulo operator

In this SQL Server article, I will share my experience with the error “SQL Operand data type real is invalid for modulo operator“. We will see, how to fix the error, sql operand data type float is invalid for modulo operator.

The modulus operator behaves differently with different data types. Recently, I was trying to use the modulus operator with different data types when I got this error.

I was trying to use the modulus operator with two real numbers. Then I got the error “Operand data type real is invalid for modulo operator“. I was trying to create a stored procedure like the below:

USE BackupDatabase
GO
CREATE PROCEDURE dbo.modulus @dividend real, @divisor real
AS
BEGIN
	DECLARE
		@result int
	SET @result = @dividend % @divisor
	PRINT('The modulus is: ' + str(@result))
END

Also, I tried to use the modulus operator on real numbers and got different error messages. However, the reasons for these errors were the same. I got the following errors while trying to use the modulus operator with real numbers:

“The data types real and numeric are incompatible in the modulo operator.”

I faced another error when I tried to use the modulus operator with a real value and an integer value.

“The data types real and int are incompatible in the modulo operator.”

Operand data type real is invalid for modulo operator

In general, we know that the modulus operator works on integers only. This is because only the division of integers leaves a remainder. The division of real numbers does not leave a remainder.

Thus, SQL Server does not support real and float dividends for the modulus operation. Only the exact numeric data types are supported. The exact numeric data types that are supported for the modulus operation are:

1. int
2. smallint
3. bigint
4. tinyint
5. numeric
6. bit
7. money
8. smallmoney
9. decimal

For example, suppose you want to get a real or float reminder, in the case when you are dealing with monetary amounts, you can use some of these exact numeric data types.

Look at the below example:

USE BackupDatabase
GO
DECLARE
	@rem real,
	@number money = 765.5
SET @rem = @number % 4
PRINT(@rem)

Related SQL server tutorials:

• How to test stored procedure in SQL Server
• SQL Server scheduled stored procedure
• SQL Server stored procedure case statement
• Alter Stored Procedure in SQL Server
• Rename stored procedure in SQL Server
• Could not find stored procedure in SQL Server
• Conversion failed when converting date and/or time from character string in SQL Server

Conclusion

It is not necessary that the modulus operator always returns an integer. It can also return float values, but you cannot use a float or real data type for a dividend.

You can use the float or real data type for the remainder only. For dividends, you have to use the exact numeric data types.

Thus, you might have learned how the modulus operator works in SQL Server with different data types.

This is how to fix the error, sql operand data type float is invalid for modulo operator.