Microsoft SQL Server Dates DATEDIFF for calculating time period differences
Example
General syntax:
DATEDIFF (datepart, datetime_expr1, datetime_expr2)
It will return a positive number if datetime_expr is in the past relative to datetime_expr2, and a negative number otherwise.
Examples
DECLARE @now DATETIME2 = GETDATE();
DECLARE @oneYearAgo DATETIME2 = DATEADD(YEAR, -1, @now);
SELECT @now --2016-07-21 14:49:50.9800000
SELECT @oneYearAgo --2015-07-21 14:49:50.9800000
SELECT DATEDIFF(YEAR, @oneYearAgo, @now) --1
SELECT DATEDIFF(QUARTER, @oneYearAgo, @now) --4
SELECT DATEDIFF(WEEK, @oneYearAgo, @now) --52
SELECT DATEDIFF(DAY, @oneYearAgo, @now) --366
SELECT DATEDIFF(HOUR, @oneYearAgo, @now) --8784
SELECT DATEDIFF(MINUTE, @oneYearAgo, @now) --527040
SELECT DATEDIFF(SECOND, @oneYearAgo, @now) --31622400
NOTE: DATEDIFF also accepts abbreviations in the datepart parameter. Use of these abbreviations is generally discouraged as they can be confusing (m vs mi, ww vs w, etc.).
DATEDIFF can also be used to determine the offset between UTC and the local time of the SQL Server. The following statement can be used to calculate the offset between UTC and local time (including timezone).
select DATEDIFF(hh, getutcdate(), getdate()) as 'CentralTimeOffset'
