In SQL Server, RIGHT_SHIFT() is a bit manipulation function that returns the first argument bit-shifted right by the number of bits specified in the second argument.
The RIGHT_SHIFT() function was introduced in SQL Server 2022.
Fix “The date value is less than the minimum date value allowed for the data type” When using SQL Server’s DATETRUNC() Function
If you’re getting SQL Server error msg 9837 that tells you “The date value is less than the minimum date value allowed for the data type…”, it sounds like you’re using the DATETRUNC() function with the week date part on a date that would cause the result to backtrack to a date that’s earlier than the date type supports.
This is a rare error that only occurs when using the week date part with the DATETRUNC() function on a very small number of early dates.
One way to deal with this error would be to use iso_week instead of week, if that’s suitable for your particular situation.
An Introduction to the LEFT_SHIFT() Function in SQL Server
In SQL Server, LEFT_SHIFT() is a bit manipulation function that returns the first argument bit-shifted left by the number of bits specified in the second argument.
The LEFT_SHIFT() function was introduced in SQL Server 2022.
Capping a Value at a Fixed Amount in SQL Server
The introduction of the LEAST() and GREATEST() functions in SQL Server 2022 were a welcome addition. These functions enable us to get the minimum or maximum value from a list of values. There are plenty of use cases for these functions.
One such use case is to provide a cap on the values returned by a query.
Continue readingFix Error 402: “The data types numeric and numeric are incompatible in the approx_percentile_disc operator” in SQL Server
If you’re getting SQL Server error 402 that reads something like “The data types numeric and numeric are incompatible in the approx_percentile_disc operator“, it’s probably because you’re trying to use the APPROX_PERCENTILE_DISC() function on a column of the wrong data type.
It could be that you’ve simply passed the wrong column, or it could be that the column is the correct one, but it’s of the wrong type.
To fix, be sure that the column/expression is of a supported type.
Continue readingUnderstanding the Difference Between MAX() and GREATEST() in SQL Server
SQL Server 2022 introduced the GREATEST() function that returns the maximum value from a list of values. You may be thinking, “but there’s already a MAX() function that returns the maximum value, so why the need for another function that does the same thing?”.
Well here’s the thing – they don’t do the same thing. They’re actually quite different functions, used in different scenarios.
If you’re wondering what the difference is between the MAX() and GREATEST() functions, read on to find out.
SQL EXISTS Explained
The SQL EXISTS predicate is used to specify a test for a non-empty set. It returns TRUE or FALSE, depending on the outcome of the test.
When we incorporate the EXISTS predicate operator into our SQL queries, we specify a subquery to test for the existence of rows. If there are any rows, then the subquery is TRUE. If there are no rows, then the subquery is FALSE.
Fix Error “The function ‘FIRST_VALUE’ must have an OVER clause” in SQL Server
If you’re getting an error that reads “The function ‘FIRST_VALUE’ must have an OVER clause” in SQL Server, it’s probably because you’re calling the FIRST_VALUE() function without an OVER clause.
The FIRST_VALUE() 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 FIRST_VALUE() function.
Fixing Error 10754: “The function ‘APPROX_PERCENTILE_DISC’ must have a WITHIN GROUP clause” in SQL Server
When using the APPROX_PERCENTILE_DISC() function in SQL Server, you may get an error that reads “The function ‘APPROX_PERCENTILE_DISC’ must have a WITHIN GROUP clause“. If you get this error, it’s because you omitted the WITHIN GROUP clause when using the APPROX_PERCENTILE_DISC() function.
To fix this issue, make sure you include the WITHIN GROUP clause whenever you use the APPROX_PERCENTILE_DISC() function.
An Introduction to the IS [NOT] DISTINCT FROM Predicate in SQL Server
SQL Server 2022 introduced the IS [NOT] DISTINCT FROM predicate that compares the equality of two expressions and guarantees a true or false result, even if one or both operands are NULL.
Normally if we compare two NULL values, they will always be different (although this will depend on your ANSI_NULLS setting – setting ANSI_NULLS to OFF will result in NULLs being treated as equal). The IS [NOT] DISTINCT FROM predicate enables us to compare NULLs as though they’re equal, even when our ANSI_NULLS setting is set to ON.