Below are three options for using T-SQL to return the first day of the month in SQL Server.
This could be the first day of the current month, or the first day of a month based on a given date.
Continue readingTag: t-sql
Fix “date is incompatible with int” in SQL Server when Adding to or Subtracting from a Date
If you’re getting error message “Msg 206” that reads “Operand type clash: date is incompatible with int” in SQL Server while trying to add to (or subtract from) a date, it’s probably because you’re trying to perform arithmetic between an integer and a date value.
To fix this issue, either change the date value to a datetime value or use the DATEADD() function.
How to Get Values That Don’t Contain Numbers in SQL Server
If you have a column in a SQL Server database table that contains character data, but some rows also contain numbers within that data, you can use the following technique to return just those rows that don’t contain numbers.
Numbers can be represented by words and other symbols, but for the purpose of this article, “number” means “numerical digit”. So we’re finding values that don’t contain any numerical digits.
Continue reading3 Ways to Delete Duplicate Rows in SQL Server while Ignoring the Primary Key
The following examples use T-SQL to delete duplicate rows in SQL Server while ignoring the primary key or unique identifier column.
More specifically, the examples delete duplicate rows but keep one. So, given two identical rows, one is deleted and the other remains. This is often referred to as “de-duping” the table, “deduplication” of the table, etc.
Continue readingHow to Return Only Numeric Values in SQL Server
In SQL Server, we can use the ISNUMERIC() function to return numeric values from a column.
We can alternatively run a separate query to return all values that contain numeric data.
Continue readingVALUES Clause in SQL Server
In SQL Server, VALUES is a table value constructor that specifies a set of row value expressions to be constructed into a table.
The VALUES clause is often used with INSERT statements to insert data, but it can also be used as a derived table in either the USING clause of the MERGE statement or the FROM clause.
Example of a Simple Merge Statement in SQL Server
The following example uses T-SQL to merge two tables in SQL Server. It uses the VALUES table value constructor as the source table.
3 Ways to Convert an Integer to Decimal in SQL Server
Here are three options for converting an integer to a decimal value in SQL Server using T-SQL.
Continue readingSQL Server SHOW TABLES Equivalent
Every now and then I find myself typing SHOW TABLES in SQL Server, expecting to get a list of tables.
That would make perfect sense if I was using MySQL or MariaDB. But SQL Server/T-SQL doesn’t have a SHOW TABLES statement like MySQL or MariaDB, so it never works. And I keep forgetting. But fortunately, SQL Server does have alternatives.
Here are five options for getting a list of tables in SQL Server. These can be used whenever you’re trying to find that elusive SHOW TABLES statement in SQL Server.
Fix Msg 512 “Subquery returned more than 1 value” in SQL Server
If you get error Msg 512 that reads “Subquery returned more than 1 value…” in SQL Server, it’s because you’re using a subquery that returns more than one value in a scenario where this is not allowed.
Continue reading