# Get Duration Between Two Dates in Years, Months, Days, and Hours using SQL Server

, 11 Jun 2013
 Rate this:
Get duration between two dates in years, months, days, and hours using SQL Server

## 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

--calculate minute
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

--calculate hours
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

--calculate days

SET @init = datepart (dd, @to) - datepart (dd, @from)
DECLARE @LastdayOfMonth INT = CASE WHEN @init<0 THEN datepart(dd,DATEADD(dd,-(DAY(@to)),@to)) _
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

--calculate Months
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

--calculate years
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!

Software Developer
India
It's amazing maths of technology...
Expand knowledge by sharing it.

 FirstPrev Next
 My vote of 1 Member 9538428 1-Jan-13 7:24
 Re: My vote of 1 Aarti Meswania 1-Jan-13 17:34
 Re: My vote of 1 Aarti Meswania 30-May-14 9:44
 My vote of 2 URVISHSUTHAR 31-Dec-12 21:08
 Re: My vote of 2 Aarti Meswania 31-Dec-12 21:17
 Re: My vote of 2 URVISHSUTHAR 31-Dec-12 21:20
 Re: My vote of 2 Aarti Meswania 31-Dec-12 21:22
 Re: My vote of 2 URVISHSUTHAR 31-Dec-12 21:24
 Run your code and check the result
 Re: My vote of 2 Aarti Meswania 31-Dec-12 21:30
 Re: My vote of 2 Aarti Meswania 31-Dec-12 22:18
 Re: My vote of 2 URVISHSUTHAR 31-Dec-12 22:22
 Re: My vote of 2 URVISHSUTHAR 31-Dec-12 22:48
 Re: My vote of 2 Aarti Meswania 31-Dec-12 22:52
 Re: My vote of 2 URVISHSUTHAR 31-Dec-12 23:04
 Re: My vote of 2 Aarti Meswania 31-Dec-12 23:11
 Re: My vote of 2 URVISHSUTHAR 31-Dec-12 23:16
 Last Visit: 31-Dec-99 18:00     Last Update: 11-Jul-14 7:15 Refresh « Prev12