sql server query for datediff
Copy - - DOES NOT ACCOUNT FOR LEAP YEARS
DECLARE @date1 DATETIME , @date2 DATETIME , @result VARCHAR (100 );
DECLARE @years INT , @months INT , @days INT ,
@hours INT , @minutes INT , @seconds INT , @milliseconds INT ;
SET @date1 = '1900-01-01 00:00:00.000'
SET @date2 = '2018-12-12 07:08:01.123'
SELECT @years = DATEDIFF (yy, @date1, @date2)
IF DATEADD (yy, - @years, @date2) < @date1
SELECT @years = @years- 1
SET @date2 = DATEADD (yy, - @years, @date2)
SELECT @months = DATEDIFF (mm, @date1, @date2)
IF DATEADD (mm, - @months, @date2) < @date1
SELECT @months= @months- 1
SET @date2= DATEADD (mm, - @months, @date2)
SELECT @days= DATEDIFF (dd, @date1, @date2)
IF DATEADD (dd, - @days, @date2) < @date1
SELECT @days= @days- 1
SET @date2= DATEADD (dd, - @days, @date2)
SELECT @hours= DATEDIFF (hh, @date1, @date2)
IF DATEADD (hh, - @hours, @date2) < @date1
SELECT @hours= @hours- 1
SET @date2= DATEADD (hh, - @hours, @date2)
SELECT @minutes= DATEDIFF (mi, @date1, @date2)
IF DATEADD (mi, - @minutes, @date2) < @date1
SELECT @minutes= @minutes- 1
SET @date2= DATEADD (mi, - @minutes, @date2)
SELECT @seconds= DATEDIFF (s, @date1, @date2)
IF DATEADD (s, - @seconds, @date2) < @date1
SELECT @seconds= @seconds- 1
SET @date2= DATEADD (s, - @seconds, @date2)
SELECT @milliseconds= DATEDIFF (ms, @date1, @date2)
SELECT @result= ISNULL (CAST (NULLIF (@years,0 ) AS VARCHAR (10 )) + ' years,' ,'' )
+ ISNULL (' ' + CAST (NULLIF (@months,0 ) AS VARCHAR (10 )) + ' months,' ,'' )
+ ISNULL (' ' + CAST (NULLIF (@days,0 ) AS VARCHAR (10 )) + ' days,' ,'' )
+ ISNULL (' ' + CAST (NULLIF (@hours,0 ) AS VARCHAR (10 )) + ' hours,' ,'' )
+ ISNULL (' ' + CAST (@minutes AS VARCHAR (10 )) + ' minutes and' ,'' )
+ ISNULL (' ' + CAST (@seconds AS VARCHAR (10 ))
+ CASE
WHEN @milliseconds > 0
THEN '.' + CAST (@milliseconds AS VARCHAR (10 ))
ELSE ''
END
+ ' seconds' ,'' )
SELECT @result