SQL Server 2016 New Date Objects

By:   |   Comments   |   Related: > SQL Server 2016


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.


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

    DATEDIFF(MILLISECOND, MIN([DateKey]), MAX([DateKey]) ) AS Spread5,
    DATEDIFF(MICROSECOND, MIN([DateKey]), MAX([DateKey]) ) AS Spread6,
    DATEDIFF(NANOSECOND, MIN([DateKey]), MAX([DateKey]) ) AS Spread7

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

    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

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

    DateKey AT TIME ZONE 'Eastern Standard Time' AS EstDateKey,
    [dbo].[FactSales] AS Fs
    [dbo].[DimStore] AS Ds ON Fs.StoreKey = Ds.StoreKey
    [dbo].[DimGeography] AS Dg ON Ds.GeographyKey = Dg.GeographyKey
    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

    current_utc_offset IN 

The output below shows the results of our query.

New SQL Server 2016 sys.time_zone_info System Table


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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author John Miner 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

Comments For This Article

get free sql tips
agree to terms