Press "Enter" to skip to content

Category: Dates and Numbers

Implicit Conversion of DATEDIFF

Published 2022-10-11 by Kevin Feasel

Daniel Hutmacher noticed a problem:

As I was performance tuning a query, I found that a number of date calculation functions in SQL Server appear to be forcing a conversion of their date parameters to a specific datatype, adding computational work to a query that uses them. In programming terms, it seems that these functions do not have “overloads”, i.e. different code paths depending on the incoming datatype.

So let’s take a closer look at how this manifests itself.

Some of these results are pretty annoying, especially because I like to use DATETIME2(0) for the large majority of development work

Comments closed

Calculating Compound Interest in Power BI

Published 2022-09-30 by Kevin Feasel

Koen Verbeeck does the math:

Recently I had an interesting use case where I had to a compound calculation in Power BI. You can compound an interest rate for example, where you get a certain rate on your savings. Let’s say 1% (which is at the time of writing ridiculously high, but bear with me). After 1 year, you get 1% interest on your money. If you leave that (small) amount of money on your savings account, you’ll get 1% after another year on the original amount + the interest amount of the previous year. This means you’re money grows exponentially (sounds more exciting than it is in reality).

Read on for an example of creating what-if parameters around compound interest rates.

Comments closed

Modulus Computations on Large Numbers

Published 2022-09-27 by Kevin Feasel

Daniel Hutmacher does the math:

The modulus is the remainder of a division of two integers*. Suppose you divide 12 by 4, the result is 3. But divide 11 by 4, and the result is 2.75. This could also be expressed by saying that 11/4 is 2 with a remainder of 3. Computing that 3 is the work of the modulo operator, which in T-SQL is represented by the % operator.

Let’s explore how to compute the modulus of large numbers in SQL Server, and how this is useful in the real world.

Daniel’s example includes IBAN validation, though I think he’s secretly working on breaking asymmetric encryption…

Comments closed

Date Calculation (and Calendar Tables) in SQL Server

Published 2022-09-15 by Kevin Feasel

Aaron Bertrand makes the case for calendar tables:

In a previous tip, Simplify Date Period Calculations in SQL Server, I described how to easily calculate certain dates, like the first day of a given month, quarter, or year. Another common scenario is trying to find the last weekday of the month, or the nth Monday or Wednesday. In this tip, I will show ways to simplify these calculations, both with and without a calendar table.

Click through for Aaron’s approach to the problem. I have a blunter approach in creating an expansive calendar table and using it. You do the heavy lifting one time and are good for life on that server.

Comments closed

Saving DateOnly and TimeOnly to Databases

Published 2022-07-14 by Kevin Feasel

Hasan Savran gives us some mixed news:

DateOnly and TimeOnly are the new members of .NET family, they are introduced in version 6. Developers have been asking to declare only a date or only a time without using the DateTime object for a long time. These two new functions will make the developer’s life easy for sure. You can declare them easily but saving them to a database can be a challenge. Before we go into those details for saving them, Let’s look at how to declare and use them in the code.

But right now, they aren’t so useful for SQL Server. Which is a shame, as there are obvious mapping candidates : DATE and TIME, respectively. My hope is that the Microsoft.Data.SqlClient library gets an update pretty soon to handle them. But read on to learn how Cosmos DB handles these new types.

Comments closed

Date-Time Binning in Cosmos DB

Published 2022-07-01 by Kevin Feasel

Hasan Savran bins some data:

I wrote about the Date_Bucket() function in SQL Server a couple weeks ago. Azure Cosmos DB team announced the same functionality with a different name DateTimeBin() function. It works exactly the same with the Date_Bucket() function of SQL Server.

     Cosmos DB version of the function has the same number of parameters. The order is different. All the datatime parameters must be in ISO 8601 format (YYYY-MM-DDThh:mm:ss.fffffffZ)

Read on to see how it works.

Comments closed

Calculating Weekdays with M

Published 2022-06-06 by Kevin Feasel

Kristyna Hughes calculates weekdays on the fly:

Knowing the days between events is a fairly common reporting request because a lot of reporting is created to track SLA’s (service level agreement) and other KPI’s (key performance indicators). While getting the days between two dates is fairly easy to achieve, they tend to follow up and ask how many week days there are between two timed events. For example, one company may have a SLA to ship an order within three week days of the order being placed or else a discount is applied to the order. In this case, I would highly recommend that the company have software that calculates these days in the background and stores the actual week days between order date and ship date in a database. Unfortunately, many companies create policies like this without considering future reporting needs and these values have to be calculated on the backend.

Click through for the code but be sure to read the note that this is all weekdays, including holidays.

Comments closed

Rounding Differences in Power BI

Published 2022-04-25 by Kevin Feasel

Marco Russo explains the importance of data types for rounding in Power BI:

In one of the last classrooms I delivered, students were wondering why the results of their formulas were close but not identical to the proposed solution. We quickly identified the problem being an issue of data type conversion already covered in Understanding numeric data type conversions in DAX. However, the issue is interesting as a simpler example to show that different DAX calculations can produce different results because of a different way of rounding numbers!

Read on for Marco’s example.

Comments closed

Time Zones and Extended Events

Published 2022-03-28 by Kevin Feasel

Tomas Zika answers a question:

I’ve helped answer another question that appeared on the SQL Server Slack:

Are timestamps in XE event files you view in SSMS local or server time?

To test this, I need a server in a different timezone than the client (SSMS). I find the quickest and most easy tool for that to be containers – more specifically, Docker.

Click through for the answer, as well as a few Docker-related incidentals.

Comments closed