Common Table Expressions (CTEs) are a handy way to break down a complex query into readable, reusable pieces. When you need several intermediate results – say, a filtered set, an aggregation, and a ranking – you can stack multiple CTE definitions together. PostgreSQL, SQL Server, MySQL 8+, and many other engines support this syntax.
DBMS
Database Management Systems
A Quick Look at SQL Server’s DATETRUNC() Function
SQL Server 2022 introduced the DATETRUNC() function, which makes working with date and time values much easier. It trims (or “truncates”) a date/time value down to a specified part (like year, month, or week) while setting all smaller units to their starting value. This helps avoid the common hack of mixing DATEADD() and DATEDIFF() just to snap a timestamp to the beginning of a period.
In this article we’ll look at some examples that demonstrate how it works.
How to Add Days and Months to Dates in SQL Server
When working with SQL Server, you may often find yourself having to shift a date by a certain number of days or months. While it sounds simple, the right function and approach can save you from subtle bugs and errors, especially when dealing with edge cases like leap years or month-end rollovers.
Let’s take a look at how to add days and months to dates in SQL Server.
Troubleshooting Date Format Errors in SQL Server Imports
Importing data into SQL Server is usually quite straightforward. That is, until you run into date and time formatting issues. Dates that look fine in a CSV, Excel, or flat file can suddenly throw errors or, worse, silently load with the wrong values. Since SQL Server is strict about how it interprets dates, mismatches between source file formats and SQL Server’s expectations are one of the most common headaches during imports.
This article looks at why these errors happen, what SQL Server expects, and how to troubleshoot these pesky date format issues.
Fixing Multiple SELECT Expressions Errors in SQL Server
If you’ve worked with SQL Server long enough, you’ve probably run into the dreaded “Only one expression can be specified in the select list when the subquery is not introduced with EXISTS” error. This error usually pops up when you try to use a subquery in a place where SQL Server expects a single value, but your query is returning multiple columns or multiple rows.
It’s a simple mistake, but it can be frustrating until you understand why it happens and how to fix it.
How to Validate Column Data Types Before Inserting Data in SQL Server
One of the most common causes of errors in SQL Server is trying to insert the wrong type of data into a column. If a column expects an integer but you push in a string, SQL Server will either throw an error or attempt an implicit conversion that may not work the way you expect. This is more common when data comes from external sources like APIs, flat files, or user inputs, where you don’t always control the formatting. Validating data types before inserting not only prevents runtime issues but also keeps your tables clean and predictable.
How to Export SQL Server Query Results as JSON with FOR JSON AUTO
SQL Server makes it surprisingly simple to generate JSON directly from a query. If you’ve ever had to send data to a web service, feed an API, or just save query results in a structured text format, JSON is a natural choice. Instead of writing complex formatting logic in your application, you can let SQL Server do the heavy lifting with the FOR JSON clause. And you can use the AUTO keyword to have the JSON formatted automatically by SQL Server.
In this article, we’ll look at how FOR JSON AUTO works, along with some examples to demonstrate its usage.
Common Causes of “Multi-Part Identifier Could Not Be Bound” in SQL Server
If you’ve worked with SQL Server for a while, you’ve probably run into the dreaded 4101 error that looks something like Msg 4104, Level 16, State 1, Line X: The multi-part identifier “X.Y” could not be bound.
It’s one of those vague errors that doesn’t immediately tell you what’s wrong. Basically SQL Server is complaining because it doesn’t know how to resolve the reference you wrote. This is usually a column or alias.
Let’s take a look at the most common causes, with examples to make them easier to spot.
Format Current Date as DD/MM/YYYY in SQL Server
If you’re in a country that uses DD/MM/YYYY format for your dates, then you’ll likely find yourself needing to display the current date in that format when you generate reports or data that’s intended to be read by humans.
Here are four methods to format the current date as DD/MM/YYYY in SQL Server.
Split and Re-Aggregate Delimited Strings in SQL Server
Working with delimited strings in SQL Server can be messy. Maybe you inherited a table where a column holds multiple values separated by commas, or you need to take a list and break it apart before putting it back together in some aggregated form. While best practice is to normalize data into proper relational tables, sometimes you don’t control the schema. Or perhaps you’re just solving a one-off reporting need.
This article walks through how to split delimited strings into rows, process them, and then re-aggregate them back into a single string, all within SQL Server.