How to Fix Error 245 When Using LEAST() or GREATEST() in SQL Server

If you’re getting error 245 that reads something like “Conversion failed when converting the varchar value ‘Five’ to data type int” when using the LEAST() or GREATEST() functions in SQL Server, it’s probably because your arguments aren’t of a comparable data type.

When using LEAST() and GREATEST(), all arguments must be of a data type that is comparable and that can be implicitly converted to the data type of the argument with the highest precedence.

To fix this issue, be sure to pass arguments of comparable data types.

Example of Error

Here’s an example of code that produces the error:

SELECT GREATEST( 'Five', 10 );

Result:

Msg 245, Level 16, State 1, Server 757cc3c95436, Line 1
Conversion failed when converting the varchar value 'Five' to data type int.

This error occurred because I passed a string and a number to the GREATEST() function.

The same thing happens with the LEAST() function:

SELECT LEAST( 'Five', 10 );

Result:

Msg 245, Level 16, State 1, Server 757cc3c95436, Line 1
Conversion failed when converting the varchar value 'Five' to data type int.

SQL Server is unable to implicitly convert the string to an integer (the int type has a higher precedence than the varchar type, and so it would try to convert the varchar to an int).

Solution 1

The most obvious solution is to make sure all arguments are of comparable data types:

SELECT GREATEST( 5, 10 );

Result:

In this case, changing 'Five' to 5 did the trick.

If you’re getting the error, it’s possible that you’re simply passing the wrong column or variable to the function (i.e. a column or variable of a different data type to the intended column or variable). If this is the case, simply changing the column name might fix the problem.

Solution 2

Another way to deal with the error is to explicitly convert one or more of the arguments to another type (so that all arguments are of a comparable type).

Example:

SELECT GREATEST( 'Five', CAST(10 AS VARCHAR(2)) );

Result:

Five

In this case I used the CAST() function to convert the second argument (an integer) to a string.