Working with dates and times in SQL Server can get tricky, especially once you add time zones into the mix. One common point of confusion is the difference between the server’s time zone and the session’s time zone. If you’re not careful, you can end up with inconsistent data or timestamps that don’t line up with what your users expect.
Let’s look at how SQL Server treats time zones and how you can handle server versus session time zone differences cleanly.
Both CONVERT() and TRY_CONVERT() in SQL Server are used to convert data types, but they behave quite differently when something goes wrong. Understanding that difference can save you a lot of debugging time, especially when dealing with messy or unpredictable data.
Let’s look at when you should use each, and walk through an example that you can run yourself.
When working with dates in SQL Server, you’ll quickly find that there are different ways to calculate the “week number” of a given date. Two of the most relevant systems are the ISO week (ISO 8601 standard) and what we’ll call the US week numbering system. If you’re working in a US environment, the latter is often the default approach (due to your session’s language setting), while ISO weeks are the international standard. These different week numbering systems will often produce different results, which can be confusing if you don’t know why.
Let’s walk through the difference, and then we’ll test it with some simple examples.
When you’re putting together reporting dashboards, raw datetime values like 2025-09-23 13:45:32.000 don’t do much for the average business user. People want to see “Sep 2025” or “Tuesday, September 23, 2025” rather than a timestamp that looks like it came straight from the database.
In many cases, formatting can also be handled in the reporting or application layer, which may be better for things like localization and display preferences. But there are plenty of situations where it makes sense to do this work in SQL Server itself. For example, maybe you need consistency or business-specific date logic. Fortunately, SQL Server gives us several tools for shaping dates into clear, readable labels that work well in dashboards.
Working with JSON data in SQL Server has become quite common as more applications are relying heavily on semi-structured data. When your JSON includes nested objects or arrays, it’s useful to know how to make precise updates without rewriting the entire document. Fortunately, SQL Server makes this relatively simple with the JSON_MODIFY() function. This function lets you update specific values inside JSON text stored in a table, even deep within the structure.
In this article, we’ll walk through an example of how to use SQL Server’s JSON_MODIFY() function to update nested JSON.
String manipulation in SQL Server can sometimes be tricky, especially when you don’t know exactly where the piece of text you need begins or ends. You might have data where the structure isn’t perfectly consistent, and you can’t rely on fixed positions. That’s where dynamic substring extraction comes in handy.
In this article we’ll look at how we can dynamically extract substrings from a string in SQL Server when we don’t know the start or end positions of those substrings, or their lengths.
Converting a string in DD/MM/YYYY format to a DATE type can be done in several ways in SQL Server. This article presents five options along with examples.
When you need to test queries, stored procedures, or reporting logic, having quick access to realistic data is imperative. While tools like INSERT...SELECT from other tables or importing CSV files are common, sometimes you just want to spin up a set of rows on the fly without messing with permanent tables. That’s where the VALUES clause can help.
The VALUES clause is usually seen in simple INSERT statements, but it can also be used directly in a SELECT statement to create inline datasets. This can be especially handy for mocking up scenarios, testing joins, or building quick prototypes.
Working with dates in SQL Server is usually quite straightforward. There’s a good range of date types and functions that we can use to manipulate date/time values.
But international date formats can undo all that simplicity in a heartbeat. Something as simple as casting a string into a DATETIME type can blow up depending on how the server interprets the input. This often happens when you’re dealing with applications or imports that don’t stick to a single culture or regional setting.
Let’s walk through an example and see why SQL Server behaves this way, and more importantly, how to handle it correctly.
When working with averages in SQL Server, it’s easy to assume that AVG() just takes all rows into account and calculates a simple mean. And that’s true. By default, AVG() includes every value in the column you point it to. But sometimes, you may want to average only unique values in that column, which is where DISTINCT comes into play.
Let’s explore this with a simple example.