New date objects in SQL Server 2016 - DATEDIFF

SQL Server 2016 obtains the values of the date and time using the GetSystemTimeAsFileTime() Windows API, with precision fixed at 100 nanoseconds. The accuracy of these date and time values depends on the hardware specs and the version of the Windows of the server on which the SQL Server instance installed.

In SQL Server 2016, new date and time objects are introduced that make it easier to deal with date and time values and with higher accuracy in nanoseconds similar to the one provided by the GetSystemTimeAsFileTime() Windows API.

The first date and time object is the DATEDIFF_BIG function that is used to evaluate the difference between two dates in the unit specified in the Datepart parameter.

The DATEDIFF_BIG function takes three parameters; datepart , startdate and enddate. The startdate and the enddate expressions can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset values. The datepart parameter can take one of the following: year, quarter, month, dayofyear, day, week, hour, minute, second, millisecond, microsecond, nanosecond values. Similar to the old DATEDIFF date and time function, the DATEDIFF_BIG function returns the count of the specified datepart boundaries crossed between the specified startdate and enddate, where the startdate will be subtracted from enddate. The difference between these two functions is the type of the result that will be returned from these date and time functions. The DATEDIFF function returns the difference between the startdate and the enddate as INT value, where the DATEDIFF_BIG function returns the difference between the startdate and the enddate as BIGINT value. The DATEDIFF_BIG function implicitly casts the string value as a datetime2 datatype. If the date value is passed as a string in the YDM format, you should explicitly cast the string to a datetime or smalldatetime datatypes, as the DATEDIFF_BIG does not support the YDM format.

The accuracy of the function result depends on the returned value data type. As you know, the value range of the integer datatype in SQL Server is from -2,147,483,648 to +2,147,483,647. So that, if we need to return a value beyond the integer range, then the DATEDIFF function is enough and will work fine. But if the returned value is more accurate and out the integer range, then this function will fail. You can think what will happen when using the Microseconds and Nanoseconds datepart with the DATEDIFF function that is out of the INT range. To overcome this limitation, SQL Server 2016 introduces the new DATEDIFF_BIG date and time function with BIGINT return data type, that will be accurate with too small datepart values, as the range of the BIGINT datatype is between -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807, which is wider than the INT datatype range. With this extended range, it will be easier to evaluate the date difference using the Microseconds and Nanoseconds dateparts. DATEDIFF_BIG function can be used within the columns list of the SELECT statement in addition to using it in the WHERE, HAVING, GROUP BY and ORDER BY clauses.

Assume that we want to get the date difference between the current date and time and the coming day using all datepart values until the millisecond datepart using the DATEDIFF function as below:

SELECT

DATEDIFF(YEAR, GETDATE(), GETDATE()+1) AS InYear,

DATEDIFF(QUARTER , GETDATE(), GETDATE()+1) AS InQuarter,

DATEDIFF(MONTH, GETDATE(), GETDATE()+1) AS InMonth,

DATEDIFF(DAYOFYEAR , GETDATE(), GETDATE()+1) AS InDayOfYear,

DATEDIFF(WEEK , GETDATE(), GETDATE()+1) AS InWeek,

DATEDIFF(DAY, GETDATE(), GETDATE()+1) AS InDays,

DATEDIFF(HOUR , GETDATE(), GETDATE()+1) AS InHour,

DATEDIFF(MINUTE, GETDATE(), GETDATE()+1) AS InMinute,

DATEDIFF(SECOND, GETDATE(), GETDATE()+1) AS InSec,

DATEDIFF(MILLISECOND, GETDATE(), GETDATE()+1 ) AS DiffInMilSec

The previous query will be executed successfully without any error and the result in our case will be like the following snapshot:

But if we try to expand the accuracy of the date difference between the current date and time and tomorrow with the microsecond and nanosecond datepart values using the DATEDIFF function:

SELECT

DATEDIFF(YEAR, GETDATE(), GETDATE()+1) AS InYear,

DATEDIFF(QUARTER , GETDATE(), GETDATE()+1) AS InQuarter,

DATEDIFF(MONTH, GETDATE(), GETDATE()+1) AS InMonth,

DATEDIFF(DAYOFYEAR , GETDATE(), GETDATE()+1) AS InDayOfYear,

DATEDIFF(WEEK , GETDATE(), GETDATE()+1) AS InWeek,

DATEDIFF(DAY, GETDATE(), GETDATE()+1) AS InDays,

DATEDIFF(HOUR , GETDATE(), GETDATE()+1) AS InHour,

DATEDIFF(MINUTE, GETDATE(), GETDATE()+1) AS InMinute,

DATEDIFF(SECOND, GETDATE(), GETDATE()+1) AS InSec,

DATEDIFF(MILLISECOND, GETDATE(), GETDATE()+1 ) AS DiffInMilSec,

DATEDIFF(MICROSECOND, GETDATE(), GETDATE()+1 ) AS DiffInMicroSec,

DATEDIFF(NANOSECOND, GETDATE(), GETDATE()+1 ) AS DiffInNanSec

An error will be thrown showing that an overflow resulted from the DATEDIFF function exceeding its returned allowed range (the maximum value of the INT datatype) and recommend you to use a less precise datepart rather than the microsecond and nanosecond datepart values in the DATEDIFF function:

If we replace the DATEDIFF function in the previous query with the new DATEDIFF_BIG function that is introduced in SQL Server 2016 to overcome the INT datatype overflow problem:

SELECT

DATEDIFF_BIG (YEAR, GETDATE(), GETDATE()+1) AS InYear,

DATEDIFF_BIG (QUARTER , GETDATE(), GETDATE()+1) AS InQuarter,

DATEDIFF_BIG (MONTH, GETDATE(), GETDATE()+1) AS InMonth,

DATEDIFF_BIG (DAYOFYEAR , GETDATE(), GETDATE()+1) AS InDayOfYear,

DATEDIFF_BIG (WEEK , GETDATE(), GETDATE()+1) AS InWeek,

DATEDIFF_BIG (DAY, GETDATE(), GETDATE()+1) AS InDays,

DATEDIFF_BIG (HOUR , GETDATE(), GETDATE()+1) AS InHour,

DATEDIFF_BIG (MINUTE, GETDATE(), GETDATE()+1) AS InMinute,

DATEDIFF_BIG (SECOND, GETDATE(), GETDATE()+1) AS InSec,

DATEDIFF_BIG (MILLISECOND, GETDATE(), GETDATE()+1 ) AS DiffInMilSec,

DATEDIFF_BIG(MICROSECOND, GETDATE(), GETDATE()+1 ) AS DiffInMicroSec,

DATEDIFF_BIG(NANOSECOND, GETDATE(), GETDATE()+1 ) AS DiffInNanSec

The query will be executed successfully without any error or overflow problem returning the date difference between the startdate and enddate with accuracy reaches to the nanoseconds, as the returned value will be with BIGINT datatype that has a wider range of values compared to the INT datatype range that is returned from the DATEDIFF function:

Another date and time object introduced in SQL Server 2016 is the AT TIME ZONE expression that can be used to transform a date time value to a given target time zone. This is a very useful for global companies with branches distributed around the world. The syntax of the AT TIME ZONE expression is simple:

inputdate AT TIME ZONE timezone

It takes an inputdate value and the name of the destination time zone then converts the inputdate to the corresponding datetimeoffset value in the specified target time zone. The inputdate value can be resolved to a smalldatetime, datetime, datetime2, or datetimeoffset value.

But how can we get the timezone name to use it in the AT TIME ZONE function? The AT TIME ZONE expression depends on the Windows mechanism in transforming the datetime values across the different time zones. It also uses the time zones that are stored in the Windows Registry under Time Zones registry path. From the Windows Date and Time page, if you click on the Change Time Zone button, you can see the full list of the time zones that are supported:

However, these time zones names are not the ones that you can use in the AT TIME ZONE expression. The correct names are the ones stored under the

KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones registry path that can be expanded as follows:

Again, it is not that easy to browse this list each time we want to search for a specific time zone and use it in the AT TIME ZONE expression. To make it easy to get all the time zones that are stored in Windows, a new DMV sys.time_zone_info is introduced in SQL Server 2016 that exposes all supported time zone information stored under the

KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones registry path. Executing the below SELECT statement:

SELECT * FROM sys.time_zone_info

This will return about 132 records of different time zones that are supported in SQL Server 2016. This information includes the time zone name, the offset of that time zone from UTC time zone and final the is_currently_dst that is True if this time zone is currently observing daylight savings time:

We can also check the current time zone for the server on which the SQL Server instance is installed using the xp_regread system object that is used to read the registry keys values as follows:

DECLARE @CurrentTimeZone NVARCHAR(100)

EXEC master.dbo.xp_regread

N'HKEY_LOCAL_MACHINE',

N'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',

N'TimeZoneKeyName',

@CurrentTimeZone OUTPUT

SELECT @CurrentTimeZone AS LocalTimeZone

The previous command executed successfully under a login with SYSADMIN rights and fails under unprivileged login. The result in my situation will be like:

The following example will list the TransactionDate column in different time zones and in the last step convert the value and display it in US Eastern time zone:

SELECT TOP 10 [TransactionID]

, [TransactionDate]

, [TransactionDate] AT TIME ZONE 'Jordan Standard Time' AS InJordanTZ

, [TransactionDate] AT TIME ZONE 'Arabian Standard Time' AS InArabTZ

, [TransactionDate] AT TIME ZONE 'US Eastern Standard Time' AS InUSTZ

, [TransactionDate] AT TIME ZONE 'Jordan Standard Time' AT TIME ZONE 'US Eastern Standard Time' AS InUSTZ2

FROM [SQLShackDemo].[Production].[TransactionHistory]

The result will be similar to:

Another example for the AT TIME ZONE usage is converting between the date times. Assume we live in Jordan with time zone GMT+2 and we need to know the current time in USA. We can easily accomplish that using the below script, that change the current time to use the time zone of Jordan, use the SWITCHOFFSET function to change the time zone offset to the UTC time zone offset, use the DATENAME function to get the USA time zone offset to use it again in the SWITCHOFFSET function to change the offset to the USA time zone offset:

DECLARE @JordanCurrentDateTime datetime

DECLARE @JordanOffset datetimeoffset

DECLARE @USADateTime datetime

DECLARE @USTimeZone nvarchar(10)

SET @JordanCurrentDateTime= GETDATE()

SELECT @JordanOffset = @JordanCurrentDateTime AT TIME ZONE 'Jordan Standard Time'

SELECT @JordanOffset AS JordanTimeWithOffset

DECLARE @UTCOffset datetimeoffset

SELECT @UTCOffset = SWITCHOFFSET(@JordanOffset, '+00:00')

SELECT @UTCOffset AS JordanTimeWithUTCOffset

SELECT @USTimeZone= DATENAME(TZ, @UTCOffset AT TIME ZONE 'US Eastern Standard Time')

SELECT @USTimeZone AS USOffset

SELECT @USADateTime = SWITCHOFFSET(@UTCOffset, @USTimeZone)

SELECT @USADateTime AS DateTimeInUSA

The output of each step will be as follows:

The combination of the AT TIME ZONE expression with the sys.time_zone_info DMV can be used to create your own dynamic calendar table that lists the current time at all supported time zones using the below simple script:

CREATE TABLE #AllTimeZones

( TZ_Name NVARCHAR(100),is_currently_dst bit)

INSERT INTO #AllTimeZones

SELECT Name , is_currently_dst FROM sys.time_zone_info

SELECT SYSUTCDATETIME() AT TIME ZONE TZ_Name as TZ_Time, TZ_Name,is_currently_dst from #AllTimeZones

The result will be like:

Conclusion:

SQL Server 2016 introduced two new date and time objects that make it easier for us to deal with the date and time values. The first function is the DATEDIFF_BIG function that overcomes the INT range limitation of the DATEDIFF function by returning BIGINT value, providing us with more accurate date and time differences. The second object is the AT TIME ZONE expression that is used to convert the current time to the target time zone specified after the AT TIME ZONE. In this article, number of examples are shown to describe how we can take benefits from these new objects.

Ahmad Yaseen

Ahmad Yaseen is a Microsoft Big Data engineer with deep knowledge and experience in SQL BI, SQL Server Database Administration and Development fields.

He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.

Also, he is contributing with his SQL tips in many blogs.

View all posts by Ahmad Yaseen

Conclusion:

Useful links:

Related posts: