## Introduction

This code shows how to get years, months, days, and hours between two dates.

## Using the Code

In SQL, there is no direct way to find out the exact difference between two dates. I hope this tip will be very useful for calculating the age of person on a specific date for validations like "age must be greater than...",

### How does it Work ?

Let us calculate the difference with an example.

We will subtract `2010-05-29 09:27`

from `2014-11-27 00:00`

**YYYY MM dd HH mm**
2014 11 27 00 00
- 2010 05 29 09 27
------------------------------------------------------------------------------
0004 06 -02 -09 -27 @init
------------------------------------------------------------------------------
0 12 31***** 24 60 @const
------------------------------------------------------------------------------
No Yes Yes Yes @init <= 0 ?
-------------------------------------------------------------------------------
31 24 60 @const
+(-02) +(-09) +(-27) + @init
+(- 1) +(- 1) +( 0) + @carry
---------- ---------- ---------- ----------
28 14 33 @result
-------------------------------------------------------------------------------
4 06 @init
0 +(-01) + @carry
------- ---------- ----------
4 05 @result
-------------------------------------------------------------------------------
0 -1 -1 -1 @carry
-------------------------------------------------------------------------------
**4 05 28 14 33 ** @output

*
* here, @const for day is 31. It can vary 28,29 or 30 as per scenario; here the difference of days is going -ve so we have consider @const = 31 because last month of bigger date is October.
*

**Note: ***Date format should be* `yyyy-MM-dd`

DECLARE @from DATETIME, @to DATETIME, @init INT, _
@carry INT, @const INT, @result INT, @output NVARCHAR(100)
SET @from ='1989-11-12 00:59'
SET @to ='1989-12-12 13:50'
SET @carry = 0
SET @output = ''
IF(@from > @to)
BEGIN
PRINT 'ERROR : Fromdate must be less than or equal to ToDate'
RETURN
END
SET @init = datepart (mi, @to) - datepart (mi, @from)
SET @const = 60
SELECT @const = CASE WHEN @init = 0 AND @carry = 0 THEN 0 else @const END
SELECT @result = CASE WHEN @init <= 0 THEN @const + @init + @carry ELSE @init + @carry END
SELECT @carry = CASE WHEN sign(@init) = -1 OR (@init = 0 AND @carry = -1) THEN -1 ELSE 0 END
SELECT @output = replicate('0',2-len(@result)) + convert(VARCHAR, @result) + ' minute(s)' + ' ' + @output
SET @init = datepart (hh, @to) - datepart (hh, @from)
SET @const = 24
SELECT @const = CASE WHEN @init = 0 AND @carry = 0 THEN 0 else @const END
SELECT @result = CASE WHEN @init <= 0 THEN @const + @init + @carry ELSE @init + @carry END
SELECT @carry = CASE WHEN sign(@init) = -1 OR (@init = 0 AND @carry = -1) THEN -1 ELSE 0 END
SELECT @output = replicate('0',2-len(@result)) + convert(VARCHAR, @result) + ' hours(s)' + ' ' + @output
SET @init = datepart (dd, @to) - datepart (dd, @from)
DECLARE @LastdayOfMonth INT = CASE WHEN @init<0 THEN datepart(dd,DATEADD(dd,-(DAY(@to)),@to)) _
ELSE datepart(dd,DATEADD(dd,-(DAY(DATEADD(mm,1,@to))),DATEADD(mm,1,@to))) END
SELECT @const = CASE WHEN @init = 0 AND @carry = 0 THEN 0 ELSE @LastdayOfMonth END
SELECT @result = CASE WHEN @init <= 0 THEN @const + @init + @carry ELSE @init + @carry END
SELECT @carry = CASE WHEN sign(@init) = -1 OR (@init = 0 AND @carry = -1) THEN -1 ELSE 0 END
SELECT @output = replicate('0',2-len(@result)) + convert(VARCHAR, @result) + ' day(s)' + ' ' + @output
SET @init = datepart (MM, @to) - datepart (MM, @from)
SET @const = 12
SELECT @const = CASE WHEN @init = 0 AND @carry = 0 THEN 0 else @const END
SELECT @result = CASE WHEN @init <= 0 THEN @const + @init + @carry ELSE @init + @carry END
SELECT @carry = CASE WHEN sign(@init) = -1 OR (@init = 0 AND @carry = -1) THEN -1 ELSE 0 END
SELECT @output = replicate('0',2-len(@result)) + convert(VARCHAR, @result) + ' month(s)' + ' ' + @output
SET @init = datepart (yy, @to) - datepart (yy, @from)
SET @const = 0
SELECT @result = CASE WHEN @carry = -1 then @const + @init + @carry ELSE @init + @carry END
SELECT @output = replicate('0',2-len(@result)) + convert(VARCHAR, @result) + ' year(s)' + ' ' + @output
PRINT @output

Happy coding!