Tag: how to

Fix “Procedure or function GENERATE_SERIES has too many arguments specified” in SQL Server

Posted on by Ian

If you’re getting error 8144 with a message that reads “Procedure or function GENERATE_SERIES has too many arguments specified” in SQL Server, it’s because you’re passing too many 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).

So to fix this error, be sure to provide either two arguments or three when using the GENERATE_SERIES() function.

Continue reading →

Fix “The date value is less than the minimum date value allowed for the data type” When using SQL Server’s DATETRUNC() Function

Posted on by Ian

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.

Continue reading →

Fix Error 402: “The data types numeric and numeric are incompatible in the approx_percentile_disc operator” in SQL Server

Posted on by Ian

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 reading →

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

Posted on by Ian

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.

Continue reading →

Fixing Error 10754: “The function ‘APPROX_PERCENTILE_DISC’ must have a WITHIN GROUP clause” in SQL Server

Posted on by Ian

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.

Continue reading →

CREATE TABLE IF NOT EXISTS Equivalent in SQL Server

Posted on by Ian

In SQL, we can use the CREATE TABLE IF NOT EXISTS statement to create a table only if it doesn’t exist. The benefit of doing this is that we won’t get an error if there’s already a table with the same name.

But SQL Server doesn’t support this syntax – at least not in the current version of SQL Server at the time of writing (SQL Server 2022) .

So with SQL Server, we need to do a bit of extra work.

Continue reading →

Fix “Argument value 0 is invalid for argument 3 of generate_series function” in SQL Server

Posted on by Ian

If you’re getting SQL Server error 4199 that reads “Argument value 0 is invalid for argument 3 of generate_series function“, it’s probably because you’re passing zero as the third argument to the GENERATE_SERIES() function.

The GENERATE_SERIES() function accepts an optional third argument, but this argument can’t be zero.

To fix this error, either pass a non-zero expression as the third argument, or omit the argument altogether (in order to use the default step of 1).

Continue reading →

Generate a Time Series in SQL Server

Posted on by Ian

SQL Server’s GENERATE_SERIES() function returns a series of numbers within a given interval. But just because it returns numbers, doesn’t mean we can’t leverage its functionality in order to generate a series of time values.

If you need to create a series of time values with evenly spaced intervals between a start and end point, maybe the following technique can help.

Continue reading →