|
The following Microsoft SQL Server
T-SQL database scripts in SSMS Query Editor demonstrate the calculation of difference between dates using the DATEDIFF and user-defined functions.
DATEDIFF returns the (signed integer) count of the datepart (year, month, day, etc.) boundaries crossed between the start date and end date.
-- T-SQL environment control statement
USE [tempdb]
GO
-- T-SQL date difference queries
SELECT Years=DATEDIFF(year, getdate(), '2011-01-01 00:00:00.0000000');
SELECT Quarters=DATEDIFF(quarter, getdate(), '2011-01-01 00:00:00.0000000');
SELECT Months=DATEDIFF(month, getdate(), '2011-01-01 00:00:00.0000000');
SELECT Days=DATEDIFF(dayofyear, getdate(), '2011-01-01 00:00:00.0000000');
SELECT Days=DATEDIFF(day, getdate(), '2011-01-01 00:00:00.0000000');
SELECT Weeks=DATEDIFF(week, getdate(), '2011-01-01 00:00:00.0000000');
SELECT Hours=DATEDIFF(hour, getdate(), '2011-01-01 00:00:00.0000000');
SELECT Minutes=DATEDIFF(minute, getdate(), '2011-01-01 00:00:00.0000000');
SELECT Seconds=DATEDIFF(second, getdate(), '2011-01-01 00:00:00.0000000');
SELECT Milliseconds=DATEDIFF(millisecond, getdate(), dateadd(hh, 2,getdate()));
SELECT Nanoseconds=convert(int, DATEDIFF(nanosecond, getdate(), dateadd(second, 2,getdate())));
-- Date difference in HH:MM:SS format
select convert(varchar,dateadd(minute, datediff(minute, getdate(), dateadd(second, 10000, getdate())),'1900-01-01'),24)
-- DATETIME subtraction maps difference to base date 1900-01-01
SELECT CONVERT(datetime,'2012-02-01 11:11:11') - '2010-12-25 01:01:02'
-- 1901-02-08 10:10:09.000
-- Delta: 1 year 1 month 8 days 10 hours 10 minutes 9 seconds
-- SQL calculate date difference in years, months, days
-- SQL table-valued function - SQL user-defined function - UDF
-- SQL datetime difference
USE AdventureWorks2008;
GO
CREATE FUNCTION fnDateDifference (@StartDate DATETIME,@EndDate DATETIME)
RETURNS @Delta TABLE(Years INT,
Months INT,
Days INT)
AS
BEGIN
DECLARE @Anniversary DATETIME
SET @Anniversary = Dateadd(yy,Datediff(yy,@StartDate,@EndDate),@StartDate)
INSERT @Delta
SELECT Datediff(yy,@StartDate,@EndDate) - (CASE
WHEN @Anniversary > @EndDate THEN 1
ELSE 0
END),
0,
0
UPDATE @Delta SET Months = Month(@EndDate - @Anniversary) - 1
UPDATE @Delta SET Days = Day(@EndDate - @Anniversary) - 1
RETURN
END
GO
-- Test table-valued UDF
SELECT *
FROM fnDateDifference ('1956-10-23', GETDATE())
SELECT *
FROM dbo.fnDateDifference ('1956-10-23', GETDATE())
/* Results
Years Months Days
52 4 1
*/
|
