Press "Enter" to skip to content

Category: Dates and Numbers

Creating a Time Zone Dimension

Jonathan Kehayias has a follow-up to a prior post:

The basis for the approach in this post is going to be the TimeZoneInfo class from .NET, which is “supposed” to be up to date with any time zone DST rules from the Windows Registry. I used a modification of the PowerShell example in the .NET Docs page for the TimeZoneInfo class to build a a flat file that is then bulk loaded to a table in SQL Server using BCP with all the time zone adjustment rules:

There’s a lot of effort in here, but as Jonathan notes, it will provide you the ability to get the right time zone as of a particular date and time. Or at least, maybe—I’m not sure if the Windows registry includes historical time zone shifts such as when the US changed when daylight savings time starts and ends.

Comments closed

Performance Issues with AT TIME ZONE

Jonathan Kehayias lays out a warning:

This is not the type of blog post that I enjoy writing. First let me start off by saying this is not a bash against Microsoft, the SQL Server team, or anything other than an informative post to let people know that there is a potential performance limitation in a very useful feature of SQL Server 2016 and higher. The AT TIME ZONE syntax was added in SQL Server 2016 to handle changing datetime values from one time zone to the offset of a different time zone using string names for the time zone. This is a great feature that simplifies converting datetime values but there is an unfortunate draw back to the implementation; it relies on the time zones that are stored in the Windows Registry and therefore has to make calls out to Windows which unfortunately occurs row-by-row for large result sets when used inside of a query.

Read on for more detail, as well as the way that Jonathan fixed the performance issue.

Comments closed

Age Calculations in Power BI

Meagan Longoria wants to calculate age:

In week 26 of Workout Wednesday for Power BI, I asked people to calculate the age of Nobel laureates at the time they received the award. I provided some logic, but I didn’t prescribe how to create the age calculation. This inspired a couple of questions and a round of data validation as calculating age may be trickier than you think. In this post, I’ll explore some of the ways people have calculated age in Power BI and the edge cases where those calculations may not work.

In my solution video for Workout Wednesday, I used Power Query to calculate age. This was inspired by several blog posts and videos I had seen previously.

This turns out to be a much trickier problem than it first appears.

Comments closed

Solving Scheduling Problems with Calendar Tables

Aaron Bertrand continues a series on the utility of calendar tables:

In two previous articles (part 1 and part 2), I showed some practical ways to use a SQL Server calendar table to solve business day and date range problems. Next, I’ll demonstrate how you can use this table to solve scheduling problems, like environment-wide server patch management.

When you have a handful of servers, managing patching is straightforward. When you have hundreds, it gets more complicated to balance keeping everything in sync and not spending all of your time organizing and performing patching.

This is a concrete example of where calendar tables can make life a lot easier.

Comments closed


Randolph West shows us how the DATETIMEOFFSET type is stored in SQL Server:

Cast your mind back to our discussion on DATETIME2. As you know, DATETIME2 is basically the same as squishing DATE (3 bytes) and TIME (between 3 and 5 bytes depending on the scale) into the same column. You end up with a persisted value that is between 6 and 8 bytes wide.

DATETIMEOFFSET is kinda sorta the same thing, but with more bytes on the end. If you take a look at the Microsoft Docs page, the similar idea of a varied column size is retained. For a scale of 0 fractions of a second you only need 8 bytes to store your value, while the default scale of 7 decimal places for storing seconds requires the full 10 bytes.

Click through to understand how the sordid details.

Comments closed

Unusual Rounding via DATETIME Math

Eitan Blumin opens Pandora’s Box:

In one of my previous posts, Fun with DATETIME Arithmetics, I introduced a way to use “math” to manipulate datetime values for effectively generating, calculating, and displaying intervals (i.e. difference between two datetime values). These mostly work with the addition and subtraction operators (+, -).

In one of the paragraphs, I mentioned multiplication and division, and posed the question about why anyone would ever need to do this.

Read on for one not-quite-ordinary reason why you might need this.

Comments closed

Using a Date Template in Power BI

Haroon Ashraf recommends using a template with date dimension details:

A Power BI Template

A Power BI template is a structure or model that typically contains commonly used tables, relationships, and hierarchies belonging to an organization or an individual. This model is reused in any Power BI report. More information is provided in the previous article:

Centralized Data Modelling using Power BI Templates

What is a Date Template in Power BI?

A Date template is a precise structure of the Date table that is a background for building reports in the organization. In other words, it is like a built-in Date table that any reports developer or a skilled business user can apply to build Power BI reports.

Read on for more Q&A as well as how to create a simple version of a date table for this template. The idea of using a template makes even more sense as you have more complicated date table requirements, such as adding in fiscal year details, holiday information (especially holidays which don’t always fall on the same solar calendar day, such as Passover or Easter), and dates important to the company.

Comments closed

Gaps and Islands in Dates

Aaron Bertrand shows off a great use for calendar tables in gap and island style queries:

In my previous article I revisited the concept of a calendar table, and explained some ways to use this data for business date calculations. This time, I wanted to explore how you can use the calendar table to simplify generating date ranges, and some query challenges this can help you simplify.

Click through for examples of the sorts of gap and island problems you can solve fairly easily with a calendar table. For an even simpler example, many BI reports want to see days even where there is no data, and a calendar table gives you that capability.

Comments closed

Arithmetic Operations on DATETIME Data Types

Eitan Bluman shows off some math skills:

Mathematical addition and subtraction can be performed between two datetime data types:

SET @d2 = '1900-03-30 18:00'SELECT@d1 + @d2 -- result: 1900-04-01 10:15:15.900, @d1 - @d2 -- result: 1899-10-05 22:15:15.900, @d2 - @d1 -- result: 1900-03-29 01:44:44.100

This means that we can have basic datetime arithmetics in SQL server. We can use subtraction to find an accurate difference between two dates, and use addition to add an accurate interval to a datetime column or variable.

This is one of those things you can do, but I’m not very fond of. First of all, as Eitan points out, you can’t do these in the (in all ways superior) DATETIME2 data type. Secondly, it adds some confusion to the code, as you don’t always get what you expect.

Comments closed