“Black Arts” of T-SQL Dates & Times #3

Abstract:
In most cases, data without dates will never be “information”. It doesn’t matter if you’re a Developer, DBA, Data Scientist, Data Analyst, or BI Ninja, virtually every type of reporting and data analysis you do will have some temporal aspect to it. And since most of us now work with millions and, sometimes, billions of rows, whatever we do, it has to be fast.
In this 3rd session on the “Black Arts” of T-SQL Dates & Times, we’re going to cover the most important aspects of reporting… grouping things by reporting periods and how to let the data calculate which reporting periods they belong to. In the end, we’ll create a report on 10 million rows of data in about 1.7 seconds using some "special sauce" (most reports of this nature take more than a minute) that contains 4 different types of reporting periods for 13 years of coverage in the same report. We'll also demonstrate the correct way to calculate week and other boundaries, in the process.

To get to that end, we’ll do a quick review on some essential knowledge covered in sessions #1 and #2, learn more about Epochs and more on DateSerial#’s, learn how to find the beginning, end, and all date parts of reporting periods, and explore a function that helps do it all. We’ll also learn the correct way of combining date and time columns (most folks do it incorrectly) and learn the special method of converting DATETIME2(3) to DATETIME without costing someone their house. Last but not least, well learn the proper way to convert two different types of UNIX timestamps to dates and times as well as the Y2K38 and other related mistakes that many (even MVPs) make during such conversions.

Speaker Bio:
Jeff Moden is a 9-year SQL Server MVP Veteran, is the longtime leading poster on SQLServerCentral.com where he has written 39 mostly 5-star articles and has more than 55 thousand post replies, has spoken at the “old” PASS Summit and many SQL Saturdays, many SQL User Groups and, lately, has given many online presentations. Some of his more heterodoxic presentations have shattered many myths that are more than 2 decades old and his article on the Tally Table has made it easy even for newbies to avoid loops and other forms of RBAR (and he coined that term, as well). Like a lot of us, he is an “accidental DBA”, has been working with T-SQL for 24 years, and has been teaching the “Black Arts” of T-SQL for the better part of that. Jeff’s mantra is “Performance is in the code”.

Agenda:
6:00-6:30 - Networking and speaker setup time
6:30-6:35 - Opening remarks & Introductions
6:35 - Speaker time!