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

Posted on by Ian

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 →

DROP TABLE IF EXISTS in SQL

Posted on by Ian

In SQL, we can use the DROP TABLE IF EXISTS statement to drop a table only if it exists.

While it may seem obvious that we can only drop a table if it exists (i.e. we can’t drop a table that doesn’t exist), there’s a good reason for using this statement.

The reason we put an IF EXISTS clause into a DROP TABLE statement is to prevent any errors that would occur if the table doesn’t exist.

Continue reading →

Create a Decrementing Series in SQL Server

Posted on by Ian

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

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

Posted on by Ian

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 →

Generate Dates Between a Date Range in SQL Server

Posted on by Ian

SQL 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.

Continue reading →

Improvements to the TRIM(), LTRIM() and RTRIM() Functions in SQL Server 2022

Posted on by Ian

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.

Continue reading →

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

Posted on by Ian

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 →

SQL Server ROLLUP() vs WITH ROLLUP

Posted on by Ian

When using the GROUP BY clause in SQL Server, we can use the ROLLUP modifier to create subtotals and grand totals, etc.

You may have seen two variations of this. One as GROUP BY ROLLUP () and the other as GROUP BY ... WITH ROLLUP.

You may be wondering which one you should use?

As it turns out, Microsoft recommends that we use the first syntax; GROUP BY ROLLUP (). The other syntax is provided for backward compatibility only.

Continue reading →

Introduction to the GENERATE_SERIES() Function in SQL Server

Posted on by Ian

In SQL Server, the GENERATE_SERIES() function is a relational operator that returns a series of values between a given start and stop point. These are returned in a single-column table.

Although the GENERATE_SERIES() function only works with numeric values, we can combine it with other functions to create a series of dates.

The GENERATE_SERIES() function was introduced in SQL Server 2022 (16.x) and requires the compatibility level to be at least 160.

Continue reading →