This article gives an introduction to the DATEDIFF and DATEDIFF_BIG functions by listing out the differences and similarities between them.
[ALSO READ]: How to get difference between two dates in Years, Months and days in Sql Server
| DATEDIFF Function | DATEDIFF_BIG Function |
| This function is available from very old versions of Sql Server. | This function is introduced in Sql Server 2016 |
| This function returns the number of the specified datepart boundaries crossed between the specified startdate and enddate | This function returns the number of the specified datepart boundaries crossed between the specified startdate and enddate |
| Syntax: DATEDIFF( datepart, startdate, enddate) Where datepart can be one of the following values: year, quarter, month, dayofyear, day, week, hour, minute, second, millisecond, microsecond and |
Syntax: DATEDIFF_BIG(datepart,startdate,enddate) Where datepart can be one of the following values: year, quarter, month, dayofyear, day, week, hour, minute, second, millisecond, microsecond and |
| This functions return value data type is INT | This functions return value data type is BigINT |
Example: Below is an example getting difference between two dates in days using DATEDIFF function:
SELECT DATEDIFF(DAY, '12/01/2015','12/02/2015') AS 'Difference in days' RESULT: |
Example: Below is an example getting difference between two dates in days using DATEDIFF_BIG function:
SELECT DATEDIFF_BIG(DAY, '12/01/2015','12/02/2015') AS 'Difference in days' RESULT: |
Example: This example demonstrates the behavior of the DATEDIFF function when the milliseconds difference between two dates is greater than the INT max (i.e. 2,147,483,647) value.
SELECT DATEDIFF(MILLISECOND, '12/01/2015','12/30/2015') AS 'Difference in MILLISECOND' RESULT: Msg 535, Level 16, State 0, Line 1 |
Example: This example demonstrates the behavior of the DATEDIFF_BIG function when the milliseconds difference between two dates is greater than the INT max (i.e. 2,147,483,647) value.
SELECT DATEDIFF_BIG(MILLISECOND, '12/01/2015','12/30/2015') AS 'Difference in MILLISECOND' RESULT: |
| The minimum and maximum value that this function can return is: -2,147,483,648 and +2,147,483,647 | The minimum and maximum value that this function can return is: -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 |
ALSO READ
- Varchar vs NVarchar
- Varchar vs Varchar(MAX)
- Char vs Varchar
- Text vs Varchar(Max)
- Union vs Union All
- DateTime vs DateTime2
- SET QUOTED_IDENTIFIER ON vs SET QUOTED_IDENTIFIER OFF
- Stored Procedure vs User Defined Function
- Primary Key vs Unique Key
- RAISERROR vs THROW
- Temporary Table vs Table Variable
- Len() vs Datalength()
- Sequence vs Identity
- DATEDIFF vs DATEDIFF_BIG
- LEFT JOIN vs LEFT OUTER JOIN
- RIGHT JOIN vs RIGHT OUTER JOIN
- JOIN vs INNER JOIN
- LEFT OUTER JOIN vs RIGHT OUTER JOIN
- SMALLDATETIME vs DATETIME
8 thoughts on “Difference between DATEDIFF and DATEDIFF_BIG functions in Sql Server”