Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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.

New feature – V | [ DATEDIFF_BIG() ]

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 –:-

YEAR
QUARTER
MONTH
DAYOFYEAR
DAY
WEEK
HOUR
MINUTE
SECOND
MILLISECOND
MICROSECOND
NANOSECOND

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-

1.https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-big-transact-sql

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements