If you’re getting error message 4112 that reads “The function ‘LAG’ must have an OVER clause with ORDER BY” in SQL Server, it’s probably because you’re omitting the ORDER BY clause from the OVER clause when using the LAG() function.
The LAG() 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.
In SQL Server, we can use the JSON_OBJECT() function to construct JSON object text from zero or more expressions.
The resulting object contains the key/value pairs that we provide as arguments. Providing zero expressions results in an empty object.
Continue readingIf you’re using the GENERATE_SERIES() function to create a series of numbers, but you’re finding that only the first value in the series is returned, it could be something very obvious.
The obvious reason this could happen is that your step value is too big. In particular, if the step is so big that it covers the whole series, then it stands to reason that there will only be one value in the series.
If this is an issue for you, you might want to check that you’re using an appropriate step value. Using a smaller step value can help to create a series with more values.
Continue readingIf you’re getting error 313 with a message that reads “An insufficient number of arguments were supplied for the procedure or function GENERATE_SERIES” in SQL Server, it’s because you’re not passing enough arguments to the GENERATE_SERIES() function.
The GENERATE_SERIES() function/relational operator accepts a minimum of two arguments, and a maximum of three (at least, this is the case at the time of writing). Passing less than two arguments will result in the above error.
To fix this error, be sure to provide at least two arguments when using the GENERATE_SERIES() function.
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 readingSQL Server 2022 introduced the GENERATE_SERIES() function that allows us to create a series of values that increment between a given start and end point.
We can create a decrementing series simply by having a greater start point than the end point, while omitting the third argument.
We can also create a decrementing series by using a negative value for the third argument. This obviously requires that the start point is higher than the stop point.
Continue readingIf 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 readingSQL Server 2022 introduced the GENERATE_SERIES() function, which enables us to create a series of values within a given range. Although this function is limited to just numeric values, we can still combine it with various other functions to create a series of date/time values.
Below are examples of how we can use the GENERATE_SERIES() function to help us get a list of all dates between two given date values.
The release of SQL Server 2022 in November 2022 introduced a bunch of new functionality, including some enhancements to the TRIM(), LTRIM() and RTRIM() functions.
The enhancements in the LTRIM() and RTRIM() functions are different to those in the TRIM() function. Below is a quick overview of the enhancements to these functions, along with examples.
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.