In SQL Server, the SUM() aggregate function returns the sum of a given expression.
It can also be used to return the sum of all distinct (unique) values in the expression.
SUM() only works on numeric columns. Null values are ignored.
Category: SQL Server
Fix Msg 8116 “Argument data type date is invalid for argument 1 of substring function” in SQL Server
If you’re getting SQL Server error Msg 8116 with text that reads Argument data type date is invalid for argument 1 of substring function, it’s because you’re passing the wrong data type to a function – in this case, the SUBSTRING() function.
You could also see the same error (Msg 8116) in many other contexts – it’s not limited to the SUBSTRING() function.
Fix Msg 8114 “Error converting data type varchar to numeric” in SQL Server
If you’re getting SQL Server error Msg 8114 that reads something like Error converting data type varchar to numeric, it’s probably because you’re trying to perform a data type conversion that fails due to the value not being able to be converted to the destination type.
It’s not because you can’t convert that type to the new type. It’s because of the value itself.
Continue readingHow TRY_CAST() Works in SQL Server
In SQL Server, an often used function is CAST(), which converts an expression of one data type to another. But if the cast doesn’t succeed, then it returns an error.
Enter TRY_CAST().
The TRY_CAST() function doesn’t return an error if the cast fails. Instead, it returns NULL.
There are some occasions however, where it will return an error.
Continue readingHow TRY_CONVERT() Works in SQL Server
In SQL Server, the TRY_CONVERT() function is very similar to the CONVERT() function, except that TRY_CONVERT() doesn’t return an error if the conversion fails (CONVERT() does).
Instead, the TRY_CONVERT() function returns NULL if the conversion doesn’t succeed.
There are some occasions however, where TRY_CONVERT() will return an error.
CONVERT() in SQL Server
In SQL Server, the CONVERT() function converts an expression of one data type to another.
Here’s a quick overview of the function with examples.
Continue readingHow CAST() Works in SQL Server
In SQL Server, the CAST() function converts an expression of one data type to another.
Here’s a quick overview of the function with examples.
Continue readingData Type Precedence in SQL Server
Below is a list containing the SQL Server data types, in order of precedence.
Continue readingHow to Format Numbers with Commas in SQL
Most of the major RDBMSs have functions that enable us to format numbers with commas as either the group separator, or the decimal separator.
Some RDBMSs also output certain numeric data types with commas in the relevant place.
Below are examples of formatting numbers with commas in some of the more popular DBMSs.
Continue reading3 Ways to Get a List of Databases in SQL Server (T-SQL)
Below are three ways we can use T-SQL to return a list of databases in SQL Server.
Continue reading