Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

SQL Server 2016 New Date Objects


By:   |   Read Comments   |   Related Tips: More > 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 Database Compatibility Level

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.

Overflow due to return 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.

New SQL Server Date Diff Big Function

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.

New SQL Server 2016 At Time Zone Expression

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.

New SQL Server 2016 sys.time_zone_info System Table

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


Last Update:






About the author
MSSQLTips author John Miner John Miner is currently a Microsoft Technology Solutions Professional (TSP) advising North East District (NED) corporations.

View all my tips
Related Resources


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools