dates

Using Window Functions with DATEDIFF() to Calculate Running Time Totals in SQL Server

by

SQL Server’s window functions allow you to perform calculations across sets of rows that are related to the current row, without collapsing those rows into a single result like traditional GROUP BY aggregates would. When combined with the DATEDIFF() function, they provide a powerful way to analyze temporal patterns in your data.

One potential use case is calculating running totals of time durations. Unlike simple aggregates that give you a single summary value, running totals show you the cumulative duration at each point in a sequence. This can be invaluable for tracking accumulated processing time, measuring cumulative delays, or understanding how total duration builds up over a series of events.

Read more

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

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

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

Convert UTC to Local Time in SQL Server

by

When dealing with applications that span multiple time zones, you’ll often want to store timestamps in UTC. That’s usually the best practice – it avoids confusion and ensures consistency. But sooner or later you’ll need to show users dates and times in their own local time zones. There are a few ways to handle this in SQL Server.

Read more

How to Handle Server vs Session Time Zone Settings in SQL Server

by

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.

Read more

Comparing ISO Week and US Week Numbers in SQL Server

by

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.

Read more

Building Readable Dates for Reporting Dashboards in SQL Server

by

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.

Read more