Problem
You were asked by your manager to investigate the date objects – DATEDIFF_BIG, AT TIME ZONE and sys.time_zone_info. Learn about the new configuration settings, T-SQL syntax and functions in this tip.
Solution
SQL Server DATEDIFF_BIG Function
Today, we are living in an era in which more data is being collected than there has ever been. Some businesses are looking to gain more insights from this data. For instance, let’s say our manager asked us to calculate the number of milli, micro and nano seconds between the first and last sale date for each store.
--- Return values > integer range
SELECT TOP 10 Storekey,
DATEDIFF(MILLISECOND, MIN([DateKey]), MAX([DateKey]) ) AS Spread5,
DATEDIFF(MICROSECOND, MIN([DateKey]), MAX([DateKey]) ) AS Spread6,
DATEDIFF(NANOSECOND, MIN([DateKey]), MAX([DateKey]) ) AS Spread7
FROM [ContosoRetailDW].[dbo].[FactSales]
GROUP BY StoreKeyThe output below shows that our query generates an overflow. The return value from the DATEDIFF function is an integer with a range of -2^31 to 2^31 – 1. Before SQL Server 2016, we would not be able to calculate this value.
SQL Server 2016 has introduced the DATEDIFF_BIG function that has a return value of a big integer with a range of -2^63 to 2^63 – 1. The query below takes advantage of this new function to calculate our results.
-- Calculate spread between dates in different units
SELECT TOP 10 Storekey,
DATEDIFF(MONTH, MIN([DateKey]), MAX([DateKey]) ) AS Spread1,
DATEDIFF(DAY, MIN([DateKey]), MAX([DateKey]) ) AS Spread2,
DATEDIFF(MINUTE, MIN([DateKey]), MAX([DateKey]) ) AS Spread3,
DATEDIFF(SECOND, MIN([DateKey]), MAX([DateKey]) ) AS Spread4,
DATEDIFF_BIG(MILLISECOND, MIN([DateKey]), MAX([DateKey]) ) AS Spread5,
DATEDIFF_BIG(MICROSECOND, MIN([DateKey]), MAX([DateKey]) ) AS Spread6,
DATEDIFF_BIG(NANOSECOND, MIN([DateKey]), MAX([DateKey]) ) AS Spread7
FROM [ContosoRetailDW].[dbo].[FactSales]
GROUP BY StoreKeyThe output below shows the results of our query.
SQL Server AT TIME ZONE Expression
Many companies now a days are part of the global community with organizational foot prints scattered across the globe. Let us assume that the sales data is stored in Greenwich Mean Time. Thus, the time zone data is a +00:00 at the end of the date time value. How can we convert the stored date and time to one that is regionally correct. In our example, we are going to single out store 114 that is in Albany, New York.
SQL Server 2016 has introduced the AT TIME ZONE expression that can modify a date time to a given target time zone. For our example, we want to choose the Eastern Standard Time zone.
--- Albany New York Store
SELECT TOP 15 Fs.StoreKey,
DateKey,
DateKey AT TIME ZONE 'Eastern Standard Time' AS EstDateKey,
Ds.StoreName,
Dg.CityName,
Dg.StateProvinceName,
Dg.RegionCountryName
FROM [dbo].[FactSales] AS Fs
INNER JOIN [dbo].[DimStore] AS Ds
ON Fs.StoreKey = Ds.StoreKey
JOIN [dbo].[DimGeography] AS Dg
ON Ds.GeographyKey = Dg.GeographyKey
WHERE Fs.StoreKey = 114The output below shows the results of our query. Please note, the minus 5 hours entries denotes day light savings time in the winter months.
SQL Server sys.time_zone_info Table
Last but not least, SQL Server 2016 has introduced the sys.time_zone_info system table. This exposes the information that was previously stored in the operating systems registry. The query below shows various time zones that fall into the United Status geography.
--- United States - Time Zones
SELECT *
FROM sys.time_zone_info
WHERE current_utc_offset IN ('-08:00','-07:00','-06:00','-05:00','-04:00')
ORDER BY current_utc_offsetThe output below shows the results of our query.
Summary
SQL Server 2016 introduced three new objects to deal with the greater demand of data. First, the DATEDIFF_BIG function allows a developer the ability to work with larger return values. Second, the AT TIME ZONE expression can convert a datetime value into a target time zone. Third, the sys.time_zone_info table exposes the operating system information to the developer.
I really like the fact that this version has added some requested functionality to the product; However, it falls short in translating an address to a time zone. How did I know that Albany, New York is in the Eastern Standard Time zone? In the future, I will talk about how to leverage a web service to convert an address or geo-coordinates into a time zone. This technique will fill this short coming.
Next Steps
- Check out these related resources:
John Miner is a Senior Data Architect at Insight Digital Innovation helping corporations solve their business needs with various data platform solutions.
He has over thirty years of data processing experience, and his architecture expertise encompasses all phases of the software project life cycle, including design, development, implementation, and maintenance of systems.
His credentials include undergraduate and graduate degrees in Computer Science from the University of Rhode Island. Also, he has earned certificates from Microsoft for Database Administration (MCDBA), System Administration (MCSA), Data Management & Analytics (MCSE), Data Science (MPP), Databricks Certified, and Fabric Certified.
John has been recognized with the Microsoft MVP award nine times for his outstanding contributions to the Data Platform community.
When he is not busy talking to local user groups or writing blog entries on new technology, he spends time with his wife and daughter enjoying outdoor activities. Some of John’s hobbies include wood working projects, crafting a good beer and playing a game of chess.
- MSSQLTips Awards: Author of the Year – 2019, 2022, 2023 | Champion (100+tips) – 2023
