Fix Error Msg 9810 “The datepart … is not supported by date function datetrunc for data type” in SQL Server

If you’re getting SQL Server error msg 9810 that tells you that the datepart “is not supported by date function datetrunc for data type“, it’s probably because you’re using an invalid datepart argument when using the DATETRUNC() function.

For example, this error can occur when using a time date part on a date value (i.e. one that doesn’t have a time component). Conversely, it can also occur when using a date date part on a time value (i.e. one that doesn’t have a date component).

Continue reading

Fix the “Operand type clash” Error when using GENERATE_SERIES() in SQL Server

If you’re getting the “Operand type clash” error when using the GENERATE_SERIES() function in SQL Server, it’s probably because your arguments aren’t of the same type.

The arguments/operands we provide to the GENERATE_SERIES() function/relational operator need to be of the same type. For example, if we pass an integer as the first argument, then the other arguments must also be an integer.

This error may also come with another error which tells us that the input parameters must be of the same type.

To fix, make sure all arguments are of the same type.

Continue reading

Fix Error “The function ‘NTILE’ must have an OVER clause” in SQL Server

If you’re getting SQL Server error 10753 that reads “The function ‘NTILE’ must have an OVER clause”, it’s probably because you’re calling the NTILE() function without an OVER clause.

The NTILE() function requires an OVER clause (and that clause must have an ORDER BY clause).

To fix this issue, be sure to include an OVER clause when calling the NTILE() function.

Continue reading

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.

Continue reading

Fix SQL Server Error 189: “The greatest function requires 1 to 254 arguments”

If you’re getting SQL Server error number 189 that reads “The greatest function requires 1 to 254 arguments“, it’s probably because you’re either passing too few or too many arguments.

As the error message alludes to, you need to pass at least 1 argument, and no more than 254 arguments when using the GREATEST() function.

To fix this issue, be sure to pass at least 1 argument, and no more than 254 arguments.

Continue reading

Fixing the Error: “The function ‘LAST_VALUE’ must have an OVER clause with ORDER BY” in SQL Server

If you’re getting an error message that reads “The function ‘LAST_VALUE’ must have an OVER clause with ORDER BY.” when using the LAST_VALUE() function in SQL Server, it’s probably because you’ve omitted the ORDER BY clause from the OVER clause.

The LAST_VALUE() function requires an OVER clause that contains an ORDER BY clause. This error happens when we include the OVER clause but not the ORDER BY clause.

To fix this error, add an ORDER BY clause to the OVER clause.

Continue reading

Fixing the “data types” are “incompatible in the approx_percentile_cont operator” in SQL Server

If you’re getting SQL Server error msg 402 that tells you the “data types” are “incompatible in the approx_percentile_cont operator“, it’s probably because you’re trying to apply the APPROX_PERCENTILE_CONT() function against a non-numeric column (or one that doesn’t evaluate to a numeric type).

When using the APPROX_PERCENTILE_CONT() function, the ORDER BY expression in the WITHIN GROUP clause must evaluate to an exact or approximate numeric type. Other data types are not allowed, and will result in the above error.

To fix this issue, be sure to apply the function against a numeric column/expression.

Continue reading

Fix SQL Server Error: “The function ‘APPROX_PERCENTILE_CONT’ must have a WITHIN GROUP clause”

If you’re getting SQL Server error 10754 that reads “The function ‘APPROX_PERCENTILE_CONT’ must have a WITHIN GROUP clause” it’s probably because you’re calling the APPROX_PERCENTILE_CONT() function, but you’ve omitted the WITHIN GROUP clause.

To fix this issue, add a WITHIN GROUP clause to the function (and make sure it has an ORDER BY clause).

Continue reading

Fix Error “The function ‘LAST_VALUE’ must have an OVER clause” in SQL Server

If you’re getting SQL Server error 10753 that reads “The function ‘LAST_VALUE’ must have an OVER clause”, it’s probably because you’re calling the LAST_VALUE() function without an OVER clause.

The LAST_VALUE() function requires an OVER clause (and that clause must have an ORDER BY clause).

To fix this issue, include an OVER clause when calling the LAST_VALUE() function.

Continue reading

Fix “Incorrect syntax near the keyword ‘DISTINCT'” Error in SQL Server

If you’re getting an error that reads “Incorrect syntax near the keyword ‘DISTINCT’” when using the DISTINCT clause in SQL Server, it could be that you’ve put the DISTINCT clause in the wrong position.

When using the DISTINCT clause, it must be the first item in the SELECT list.

Therefore, to fix this error, check the position of the DISTINCT keyword. If it’s not the first item in the SELECT list, move it to the front so that it is the first item in the SELECT list.

Continue reading