Fixing "Msg 402" in SQL Server: Understanding Modulo and Data Types

If you've been working with SQL Server and ran into this error:

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

Don't worry — you're not alone. This is a common issue and it's actually quite easy to fix once you understand what's going on.

Let’s break it down in simple terms.

What Does This Error Mean?

This error is telling you that SQL Server doesn’t allow you to use the modulo operator (%) with FLOAT and INT data types together.

Wait… What’s a Modulo?

The modulo operator (%) gives you the remainder after dividing one number by another.

For example:

SELECT 10 % 3 -- Result is 1

That means:

10 divided by 3 = 3 with a remainder of 1

The % operator works perfectly with integers (whole numbers).

The Problem: Mixing Float with Int

Let’s say you write something like:

DECLARE @num1 FLOAT = 10.5
DECLARE @num2 INT = 3
SELECT @num1 % @num2

SQL Server throws the error:

"The data types float and int are incompatible in the modulo operator."

Why?

Because SQL Server is very strict about the % operator. It only works with integers.

INT % INT ➡️ ✅ Works
FLOAT % INT ➡️ ❌ Error
FLOAT % FLOAT ➡️ ❌ Error

The Fix: Convert to Integers

If you only care about the remainder of the whole number part, you can cast your float to an integer using CAST() or CONVERT().

Example:

DECLARE @num1 FLOAT = 10.5
DECLARE @num2 INT = 3
SELECT CAST(@num1 AS INT) % @num2 -- Output: 1

What’s Happening Here?

CAST(@num1 AS INT) converts 10.5 → 10
Then 10 % 3 gives 1

Note: Casting a float to an int truncates the decimal part. It does not round. So:

10.9 → 10
10.1 → 10

If you need to do math on the decimal part, the modulo operator might not be the right tool — consider other math functions.

Summary

Here’s what you need to remember:

SQL Server doesn't support % on FLOATs
Always use integers with the % operator
If you have FLOATs, use CAST() to convert them

Simple Rule:

Always use integers with % — cast if needed!

Final Example

-- Incorrect: Will throw Msg 402
SELECT 10.5 % 3

-- Correct: Cast float to int
SELECT CAST(10.5 AS INT) % 3 -- Output: 1

Wrapping Up

This is one of those SQL quirks that can be confusing at first, but once you get the hang of it, it’s pretty straightforward. Understanding how SQL Server handles data types — especially with operators — is a great step toward writing better and more reliable queries.