Lightning Talk
The new PowerBI Projects deployment approach by Mehul K Bhuva.
linkedin.com/in/mehulbhuva
sharepointfix.com
Mehul Bhuva is a seasoned Data Architect with 20 years of expertise in Azure Synapse Analytics, Azure Data Factory, Azure Data Lake Gen 2, Azure Databricks, .NET Core, C#.NET, Blazor, Angular, Power BI, and MS-SQL.
Main Presentation (begins at the 24:15 mark)
In an age where precision and modern data types reign supreme, SQL Server’s DATETIME2 and other modern temporal datatypes are often hailed as the future for being more accurate, more flexible, and more standards-compliant than their predecessor, the DATETIME datatype.
So why do so many experienced Developers, DBAs, Data Analysts, and Data Engineers still reach for DATETIME?
The reasons are many and frequently include simplicity of code, enhanced functionality, and fairly significant increases in performance. For example, it’s much easier to calculate large, fine resolution durations and “totals of durations” in DATETIME than it is in DATETIME2 especially since there’s no DATEADD_BIG() until SQL Server 2025 comes out. You can also get as much as a 60% improvement in performance on many common temporal calculations by using DATETIME instead of the more modern temporal datatypes.
The key to much of it is the “simple” conversion of DATETIME2 to DATETIME.
Unfortunately, there are sometimes catastrophic “Silent Failures” in converting DATETIME2 (for example) to DATETIME where the result can be rounded up even to the next year with absolutely no indication that such a significant error has taken place. We’ll learn how to easily overcome that problem with a simple tweak to the conversion. We’ll also correct the mistake in the Microsoft documentation and learn how to correctly truncate to lower resolutions without the use of the fairly new DATETRUNC() function, which wasn’t available until 2022.
There are many temporal data tricks to be had in SQL and other tools but the key to success is frequently the PROPER conversion between temporal datatypes. The purpose of this session is to introduce or reintroduce the wonders and performance advantages of the DATETIME datatype and how to correctly convert other datatypes to DATETIME.