Click here to Skip to main content
Click here to Skip to main content

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

, 11 Jun 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
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)) _
    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

--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! Smile | :)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

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

Comments and Discussions

 
GeneralRe: My vote of 2 PinmemberURVISHSUTHAR31-Dec-12 21:24 
AnswerRe: My vote of 2 PinmemberAarti Meswania31-Dec-12 21:30 
AnswerRe: My vote of 2 PinmemberAarti Meswania31-Dec-12 22:18 
GeneralRe: My vote of 2 PinmemberURVISHSUTHAR31-Dec-12 22:22 
Will check for you Smile | :)
GeneralRe: My vote of 2 PinmemberURVISHSUTHAR31-Dec-12 22:48 
AnswerRe: My vote of 2 PinmemberAarti Meswania31-Dec-12 22:52 
GeneralRe: My vote of 2 PinmemberURVISHSUTHAR31-Dec-12 23:04 
GeneralRe: My vote of 2 PinmemberAarti Meswania31-Dec-12 23:11 
GeneralRe: My vote of 2 PinmemberURVISHSUTHAR31-Dec-12 23:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.141029.1 | Last Updated 11 Jun 2013
Article Copyright 2012 by Aarti Meswania
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid