Press "Enter" to skip to content

Category: Dates and Numbers

Building an Event Calendar from T-SQL and CSS

Published 2023-02-22 by Kevin Feasel

Aaron Bertrand builds some HTML:

In my last tip, I showed how to use T-SQL to generate HTML for fancy calendar visuals overlaid with event data from another table. As an extension of that tip, let’s now look at simplifying parts of that query by caching the date information in a calendar table to streamline the outer queries and avoid complications caused by different DATEFIRST settings.

This is the follow-on from a prior post (linked in the lede here), so it would make sense to read that one first if you haven’t already.

Comments closed

Approximation with the Mediant

Published 2023-02-08 by Kevin Feasel

John Cook didn’t make a typo:

Suppose you are trying to approximate some number x and you’ve got it sandwiched between two rational numbers:

a/b < x < c/d.

Now you’d like a better approximation. What would you do?

The obvious approach would be to take the average of a/b and c/d. That’s fine, except it could be a fair amount of work if you’re doing this in your head.

Read on for a separate approach taking the mediant (not median) of the two fractions.

Comments closed

Regionally Formatted Dates in Excel and Power BI

Published 2023-02-02 by Kevin Feasel

Allison Kennedy wrangles dates:

Dates are fundamental to pretty much every report. No matter what industry you work in, at some stage you’re going to work with dates in your reporting. This might be in the form of Semesters, Quarters, Seasons, Weeks, or just good old fashioned Dates. 

If you’re working with Power Query or Power BI, you should have a Date Table. In this post, I’m going to demonstrate how to work with Dates that can be tricky to format. 

Click through for examples of three common challenges when working with dates in Excel and Power BI.

Comments closed

Testing BIGINT Support in Applications

Published 2023-01-04 by Kevin Feasel

Michael J. Swart reminds us that it’s not just the database which needs to be able to handle large values:

In the past I’ve written about monitoring identity columns to ensure there’s room to grow.

But there’s a related danger that’s a little more subtle. Say you have a table whose identity column is an 8-byte bigint. An application that converts those values to a 4-byte integer will not always fail! Those applications will only fail if the value is larger than 2,147,483,647.

This post specifically pertains to identity columns but don’t forget those non-identity columns when testing.

Comments closed

Time Zone Conversion in SQL Server

Published 2022-11-22 by Kevin Feasel

Ed Pollack wants to know what time it is:

Converting a current time from one time zone to another is relatively easy. Regardless of whether daylight savings is involved or not, one simply needs to retrieve the current time in both time zones, find the difference, and apply that difference as needed to date/time calculations. Historical data is trickier, though, as times from the past may cross different daylight savings boundaries.

This article dives into all the math required to convert historical times between time zones. While seemingly academic in nature, this information can be used when building applications that interact between time zones and need to apply detailed rules to those applications and their users. These calculations will be demonstrated in T-SQL and a function built that can help in handling the math for you.

The pro tip is to store all data in UTC and perform date and time calculations at the edge, where you know the user’s time zone. Ed has plenty of good advice in here as well.

Comments closed

DATE_BUCKET and DATETRUNC in SQL Server 2022

Published 2022-10-19 by Kevin Feasel

Itzik Ben-Gan shows a good use of a pair of new T-SQL functions:

Time-based grouping and aggregation are common in analyzing data using T-SQL—for example, grouping sales orders by year or by week and computing order counts per group. When you apply time-based grouping, you often group the data by expressions that manipulate date and time columns with functions such as YEAR, MONTH, and DATEPART. Such manipulation typically inhibits the optimizer’s ability to rely on index order. Before SQL Server 2022, there was a workaround that enabled relying on index order, but besides being quite ugly, it had its cost, and the tradeoff wasn’t always acceptable.

Comments closed

Date Ranges and Merge Interval

Published 2022-10-17 by Kevin Feasel

Daniel Hutmacher notes a performance killer:

In my last post, I found that DATEDIFF, DATEADD and the other date functions in SQL Server are not as datatype agnostic as the documentation would have you believe. Those functions would perform an implicit datatype conversion to either datetimeoffset or datetime (!), which would noticeably affect the CPU time of a query.

Well, today I was building a query on an indexed date range, and the execution plan contained a Merge Interval operator. Turns out, this operator brings a few unexpected surprises to your query performance. The good news is, it’s a relatively simple fix.

Click through for an example and some information on a fix. Hugo Kornelis also adds some good insights in the comments.

Comments closed

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