calculate number years months days between 2 dates

[Louise Harrison]

I had read a previous article on calculating the number of years, months and days between 2 dates. They had used the DateDiff with a start date and an end date, similiar like below statement:

DateDiff("d",Da teAdd("m",[Months],DateAdd("yyyy" ,[Years],[DateHired])),[DateTerminated]) AS Days

When I have dates like 2011-05-09 for the DateHired and 2011-09-08 for DateTerminated the days come out as a -1.
Here is another example: For the DateHired 2009-06-30 and the DateTerminated 2011-06-15 the days come out as -15.
One more example: DateHired 2008-09-29 and DateTerminated 2011-09-23 the days come out as -6.

I am able to get the years and months correctly but the some of the days end up being negative and incorrect.

[TheSmileyCoder]

Am I correct in understand that you need each value calculated seperately? It is not enough for you to simply have the value in days? Is that the issue?

[NeoPa]

Just as an introduction, this will probably work most reliably using the Year(), Month() & Day() function values for each date. It seems, from your question, that you are trying to work in SQL. A good idea to mention that generally, but I think we can assume from what you let slip.

The following should get you somewhere quite close :

SELECT Year([DateTerminated]) - Year([DateHired]) -
       IIf(Month([DateTerminated]) = Month([DateHired]),
       IIf(Day([DateTerminated]) < Day([DateHired]), 1,
       IIf(Month([DateTerminated]) < Month([DateHired]), 1, 0))) AS [Years]
     , (12 + Month([DateTerminated]) - Month([DateHired]) -
       IIf(Day([DateTerminated]) < Day([DateHired]), 1, 0)) Mod 12 AS [Months]
     , Day(DateAdd('d', -Day([DateHired]), DateAdd('m', 1, [DateHired]))) AS [DIM]
     , ([DIM] + Day([DateTerminated]) - Day([DateHired])) Mod [DIM] AS [Days]

Let us know how well that works for you.