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:
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
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.
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.