By: John Miner | Comments | Related: > SQL Server 2016
Problem
You were asked by your manager to investigate the new date objects that were released with SQL Server 2016 this past June. Learn about the new configuration settings, T-SQL syntax and functions in this tip.
Solution
In order to test this new functionality, if you don't have a SQL Server 2016 environment, I suggest setting up a virtual test machine in Azure with the Contoso Retail DW database installed. Read this tip to accomplish those tasks.
SQL Server Database Compatibility
Before we can play with the new SQL Server 2016 date objects, we need change the compatibility level of the database. The Contoso sample database was created for SQL Server 2008. However, the new objects are available in SQL Server 2016.
We can use SQL Server Management Studio to change the compatibility level of the database. Right click the database name in the object explorer, choose properties, and then choose options. Change the level to 130 and click save.
SQL Server 2016 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
StoreKey
The 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
StoreKey
The output below shows the results of our query.
SQL Server 2016 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
JOIN
[dbo].[DimStore] AS Ds ON Fs.StoreKey = Ds.StoreKey
JOIN
[dbo].[DimGeography] AS Dg ON Ds.GeographyKey = Dg.GeographyKey
WHERE
Fs.StoreKey = 114
The 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 2016 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_offset
The 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:
About the author
John Miner is a Data Architect at Insight Digital Innovation helping corporations solve their business needs with various data platform solutions.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
