A Fine Slice Of SQL Server
Kevin Wilkie is ready for that end-of-month paycheck:
When you work with data, you’ll probably need to work with dates at least once a month. That is the nature of the beast. Today, let’s compare working with them in SQL Server and Snowflake. I want to focus only on adding and subtracting months when provided with a specific day.
Along the way, I would also push for a calendar table, so that you can remove some of the more difficult (or even most common) date calculations.
Comments closedI have been teaching a T-SQL 101 class and for the homework, we asked the students to get all the records where our heroes had a birthdate between 1995 through 1999. I expected something like this:
[…]
Imagine my surprise when one of the students turned in this:
SELECT FirstName, LastName, Birthdate FROM Heroes WHERE Birthdate BETWEEN '1995' AND '1999'When I first saw the query I thought, “There is no way they ran that and it worked.” So I wrote it up and ran it on my data. Guess what? IT RUNS AND RETURNS DATA! I was shocked.
Click through to see what it returns and how that’s not quite right.
Comments closedAaron 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
DATEFIRSTsettings.
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 closedSuppose 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 closedAllison 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 closedIn 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 closedEd 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 closedHasan Savran won’t settle for just one date:
GENERATE_SERIES function is introduced in SQL Server 2022 version. It is a simple function that generates a series of numbers within a given interval. It requires compatibility level 160.
The natural use for it is to generate a set of numbers, but as Hasan shows, you can easily turn that into a set of dates or dates and times.
Comments closedItzik Ben-Gan shows a good use of a pair of new T-SQL functions:
Comments closedTime-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.
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