NEW T-SQL FEATURES IN SQL SERVER 2016- V [ DATEDIFF_BIG() ]

In this post we shall check out the new features Microsoft introduced in SQL Server 2016.

Returns the count (signed big integer) of the specified datepart boundaries crossed between the specified startdate and enddate. DATEDIFF_BIG implicitly casts string literals as a datetime2 type. This means that DATEDIFF_BIG does not support the format YDM when the date is passed as a string. You must explicitly cast the string to a datetime or smalldatetime type to use the YDM format.

Notes:-

1. If the return value is out of range for bigint it returns the error.
2. Range is -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
3. The returns value is NULL if any of the date input is NULL.
4. This function can be used in the select list, WHERE, HAVING, GROUP BY and ORDER BY clauses.
5. Specifying SET DATEFIRST has no effect on DATEDIFF_BIG. DATEDIFF_BIG always uses Sunday as the first day of the week to ensure the function is deterministic.

DATEPART can be –:-

Sample example for [ DATEDIFF_BIG() ]

--

SELECT DATEDIFF_BIG(YEAR, DATEADD(YEAR,-1,GETDATE()), GETDATE());

--

--

--------------------
1

(1 row affected)

--

--

SELECT DATEDIFF_BIG(MONTH, DATEADD(YEAR,-1,GETDATE()), GETDATE());

--

--

--------------------
12

(1 row affected)


--

--

SELECT DATEDIFF_BIG(SECOND, DATEADD(YEAR,-1,GETDATE()), GETDATE());


--

--

--------------------
31536000

(1 row affected)


--

--

SELECT DATEDIFF_BIG(MILLISECOND, DATEADD(YEAR,-1,GETDATE()), GETDATE());

--

--

--------------------
31536000000

(1 row affected)


--

--

SELECT DATEDIFF_BIG(NANOSECOND, DATEADD(YEAR,-1,GETDATE()), GETDATE());

--

--

--------------------
31536000000000000

(1 row affected)

--

Refer below for more details-

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com