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.
If you have a query that returns multiple columns with numeric values in SQL Server, you can pass them to the GREATEST() function to find out which column has the highest value.
The release of SQL Server 2022 came with the introduction of the DATE_BUCKET() function.
The DATE_BUCKET() function allows us to arrange data into groups that represent fixed intervals of time. It returns the date/time value that corresponds to the start of each date/time bucket, as defined by the arguments passed to the function.
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.
In SQL NULL is a special value, or mark, that is used to indicate the absence of any data value. And this is the case with SQL Server.
But SQL Server also has a special use of NULL in certain cases.
In SQL Server, we can use the DATETRUNC() function to truncate a date/time value to a specified precision.
For example, we could use it to truncate a date value like 2024-10-25 to 2024-01-01, or a time value like 10:35:12 to 10:00:00.
The DATETRUNC() function was introduced in SQL Server 2022 (16.x).
In SQL Server, we can use the TRIM() function to remove leading and trailing characters from a given string.
A common use case is to remove whitespace from the start and end of the string, but we can also specify other characters to remove.
Also, as from SQL Server 2022, we can specify which side of the string to remove the characters from (i.e. leading, trailing, or both).
In SQL Server, we can use the T-SQL RTRIM() function to remove trailing blanks from a given string. Trailing blanks are white spaces, tabs, etc that come at the end of the string.
Also, as from SQL Server 2022, we can specify other trailing characters to remove from the string.
In SQL Server, we can use the T-SQL LTRIM() function to remove leading blanks from a given string.
Also, as from SQL Server 2022, we can specify other leading characters to remove from the string.
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.