mssql

Calculating Cumulative Offsets with DATEADD() and Window Aggregates in SQL Server

by

Window functions in SQL Server aren’t just about ranking and numbering rows. When you combine aggregate window functions with DATEADD(), you can create running totals that translate into meaningful date calculations. This approach is particularly valuable when you need to calculate delivery schedules, project timelines, or any scenario where accumulated values should push dates further into the future.

The pattern involves using SUM() or another aggregate with the OVER clause to create a running total, then feeding that total into DATEADD() to offset a base date. The result is a dynamic date that reflects the cumulative impact of your data. Let’s explore this with a simple example.

Read more

How to Convert JSON to Rows and Columns in SQL Server

by

Modern applications often exchange information in JSON, and that data often ends up in SQL Server. While JSON’s flexible structure makes it ideal for storing dynamic or nested data, it doesn’t fit neatly into traditional relational tables. The good news is that SQL Server includes a good selection of JSON functions that let you parse, query, and transform JSON content into structured rows and columns. This means that you can work with your JSON data just like any other table.

Read more

Calculating Past Dates in SQL Server

by

Working with dates is one of those things you’ll do constantly in SQL Server, and knowing how to calculate past dates efficiently can save you a ton of time. Whether you’re pulling last month’s sales data or filtering records from the past week, SQL Server gives you several straightforward ways to handle date calculations.

Read more

Using CAST() to Convert Rounded Values to Integers in SQL Server

by

Sometimes when you’re working with calculated columns in SQL Server, you might get results in a data type that’s less than ideal. For example, maybe it’s a decimal or float when you really need an integer. This can result in the output not appearing exactly the way you want, such as with a bunch of unnecessary trailing decimal zeros.

In such cases, you’ll probably want to remove these trailing zeros from the result. The CAST() function is perfect for this kind of cleanup. You can use it to convert the value to a more suitable type.

Read more

How to Conditionally Drop Objects Before Recreating Them in SQL Server

by

Recreating objects like tables, views, stored procedures, or functions is quite common when developing databases. Maybe you’re iterating on a design, maybe you’re fixing a bug, or maybe you just need a clean slate. The problem is that SQL Server will throw an error if you try to create an object that already exists. To avoid that, you’ll need a reliable way to conditionally drop the object before recreating it.

And this isn’t just a matter of convenience. It helps keep scripts idempotent, meaning you can run them multiple times without worrying about errors or leftover objects from previous runs.

Fortunately SQL Server provides us with at least two easy options for doing this.

Read more

Handling Month Names in Different Languages in SQL Server

by

If your database serves users in different regions, controlling how month names appear is one of those small but important details. Maybe you’re generating reports for users across regions, or exporting data that needs to match a specific locale. Whatever the case, sometimes you just need SQL Server to show month names in a different language.

This article walks through how SQL Server handles month names under different language and locale settings, and how you can control that behavior.

Read more

Displaying Abbreviated and Full Day Names for Reports in SQL Server

by

When building reports in SQL Server, dates are probably one of the most common pieces of data you’ll deal with. Sometimes a report needs the full day name like “Monday”, while in other cases a short form like “Mon” is preferred, often to save space. Luckily, SQL Server has built-in functionality to handle both, without having to manually map numbers to names.

Let’s look at how we can display abbreviated and full day names in queries so that our reports are nice and easy to read.

Read more

Using “GO” to Structure T-SQL Batches

by

If you spend much time writing T-SQL scripts, you’ve probably seen the GO keyword. It looks like a T-SQL command, but it’s not really part of T-SQL. Instead, it’s a batch separator recognized by SQL Server Management Studio (SSMS) and other client tools. When you hit the “Execute” button, any GO keyword in your script tells the tool that this is the end of a batch, and to send what came before it to SQL Server as one unit.

Many scripts will run fine without the GO keyword, but others will fail miserably. Understanding how GO works can save you from frustrating errors and unexpected behavior.

Read more

How to Identify Foreign Keys with Cascade Action in SQL Server

by

When working with relational databases in SQL Server, foreign keys help maintain referential integrity between tables. Sometimes these constraints are defined with actions such as CASCADE, which automatically propagates updates or deletions from a parent table to related rows in a child table.

While this functionality can be useful, it can also introduce unexpected side effects if you are not aware of where it is enabled. Knowing how to identify foreign keys with cascade actions is an important part of understanding data dependencies, troubleshooting issues, and ensuring database operations behave as intended.

Read more