none
Difference between Two Dates

    Question

  • How to find the exact Difference between Two Dates , The result  will be like 2 Years 3 months 15 days.
    Tuesday, April 26, 2011 1:00 PM

Answers

  • hi,

    as I wrote: no trivial ;)

    DECLARE @first DATE = '2009-01-25' ;
    DECLARE @second DATE = '2010-04-01' ;
    
    DECLARE @y INT , @m INT , @d INT ;
    
    SET @y = DATEDIFF(YEAR, @first, @second) ;
    IF ( @y > 0 )
        SET @first = DATEADD(YEAR, @y, @first) ;
    
    SET @m = DATEDIFF(MONTH, @first, @second) ;
    IF ( @m > 0 )
        SET @first = DATEADD(MONTH, @m, @first) ;
    
    SET @d = DATEDIFF(DAY, @first, @second) ;
    IF ( @d < 0 )
        BEGIN
            SET @first = DATEADD(MONTH, -1, @first) ;
            SET @d = DATEDIFF(DAY, @first, @second) ;
        END ;
    
    SELECT  @y , @m , @d ;

    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Wednesday, April 27, 2011 3:18 PM

All replies

  • hi,

    this not trivial to do. You can calculate the difference in days using DATEDIFF():

    http://msdn.microsoft.com/en-us/library/ms189794.aspx

    Then you need to format it according to your needs when using a fixed time frame, e.g. a fiscal year of 360 days with month of 30 days.

    But on the other side you may a real world "difference", then you need to think about leap years. Then you need to do it in a three step operation using DATEADD():

    http://msdn.microsoft.com/en-us/library/ms186819.aspx

    E.g.

    DECLARE @first DATE = '2009-01-25' ;
    DECLARE @second DATE = '2010-04-25' ;
    
    DECLARE @y INT , @m INT , @d INT ;
    
    SET @y = DATEDIFF(YEAR, @first, @second) ;
    IF ( @y >= 1 )
      SET @first = DATEADD(YEAR, @y, @first) ;
    
    SET @m = DATEDIFF(MONTH, @first, @second) ;
    IF ( @m >= 1 )
      SET @first = DATEADD(MONTH, @m, @first) ;
    
    SET @d = DATEDIFF(DAY, @first, @second) ;
    
    SELECT @y , @m , @d ;

    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Tuesday, April 26, 2011 1:29 PM
  • Hi 

    Stefan Hoffmann

    Thanks for your reply, but it is giving some  negative values for particular inputs

     

    check it for these inputs   @first='2009-01-25' and @second='2010-04-01' 

     

    it is giving output as 1 year 3 months -24 days.

    Wednesday, April 27, 2011 2:56 PM
  • hi,

    as I wrote: no trivial ;)

    DECLARE @first DATE = '2009-01-25' ;
    DECLARE @second DATE = '2010-04-01' ;
    
    DECLARE @y INT , @m INT , @d INT ;
    
    SET @y = DATEDIFF(YEAR, @first, @second) ;
    IF ( @y > 0 )
        SET @first = DATEADD(YEAR, @y, @first) ;
    
    SET @m = DATEDIFF(MONTH, @first, @second) ;
    IF ( @m > 0 )
        SET @first = DATEADD(MONTH, @m, @first) ;
    
    SET @d = DATEDIFF(DAY, @first, @second) ;
    IF ( @d < 0 )
        BEGIN
            SET @first = DATEADD(MONTH, -1, @first) ;
            SET @d = DATEDIFF(DAY, @first, @second) ;
        END ;
    
    SELECT  @y , @m , @d ;

    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Wednesday, April 27, 2011 3:18 PM
  • I'm afraid when @first is changed to '2008-12-25' the answer should be 1 year 4 months 7 days, but the result is :-

    2 years, -8 months, -238 days.

    Time for a rethink.........?

    Monday, March 18, 2013 1:56 PM