Press "Enter" to skip to content

Category: Dates and Numbers

Rounding Times in SQL Server

Published 2021-08-09 by Kevin Feasel

Steve Stedman has the low-down on time rounding in SQL Server:

One thing that I end up having to look search on regularly is rounding of dates and times in Transact SQL, having looked this up too many times I finally realized that it is time for me to do my own blog post for it.

First off, whats the difference between rounding and truncating in these examples. Rounding rounds to the closest second, so 10:00:31 is rounded up to 10:01:00, and 10:00:29 is rounded down to 10:00:00. With truncation, it simple changes the truncated area to 0’s. so 10:00:31 gets truncated down to 10:00:00, and so does 10:00:59. Sometimes you may want rounding, and sometimes you may want truncation (floor) for your specific needs.

After having used date_trunc() in Postgres, I’d really like something similar in SQL Server.

Comments closed

Representing Dates in Power BI: Date or Integer?

Published 2021-08-05 by Kevin Feasel

Marco Russo and Alberto Ferrari share their take on a classic debate:

A question that is often asked during the design of a Power BI data model is whether it is better to use an Integer or a Datetime column to link a fact table with the Date dimension. Historically, using Integers has always been a better choice in database design. However, Tabular is an in-memory columnar database, and its architecture is quite different from the relational databases we might be used to working with.

Indeed, in Tabular there are no technical differences between using a Datetime or an Integer to create a relationship. The database size, the query speed, and any other technical detail are absolutely identical. Therefore, the choice is not related to technical aspects, but rather on the convenience of the design. Depending on the specific requirements of your model, you might favor one data type against the other. In the most common scenarios, a Datetime proves to be better because it provides more possibilities to compute values on dates without having to rely on relationships. With that said, if your model uses Integers and you do not need to perform calculations on the dates represented in the table, then you can choose the most convenient data type – that is, the one already used in the original data source.

The remaining part of the article aims to prove the previous sentences, and to provide you with the technical details about how we tested the respective performance of the two options.

Click through for Marco and Alberto’s analysis, noting that “date” here does not include time of day, so it would have the same cardinality as the integer date key. This was a more important thing fifteen years ago, before columnstore technologies (like columnstore indexes and VertiPaq) were readily available and that 4-byte integer was considerably smaller than an 8-byte DATETIME.

Comments closed

Creating a Time Zone Dimension

Published 2021-08-02 by Kevin Feasel

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

Published 2021-07-23 by Kevin Feasel

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

Published 2021-07-12 by Kevin Feasel

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

Published 2021-06-17 by Kevin Feasel

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

Storing DATETIMEOFFSETs

Published 2021-06-17 by Kevin Feasel

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

Published 2021-06-17 by Kevin Feasel

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

Published 2021-05-05 by Kevin Feasel

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