# 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