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
 Re: Problem dates Aarti Meswania 30-May-14 9:43
 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
 which are inputs for birthdate and AsOnDate ?
 Re: My vote of 2 URVISHSUTHAR 31-Dec-12 21:24
 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: 10-Jul-14 5:40 Refresh « Prev12