By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,490 Members | 1,789 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,490 IT Pros & Developers. It's quick & easy.

calculate number years months days between 2 dates

P: 1
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",DateAdd("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.
Dec 28 '11 #1
Share this Question
Share on Google+
2 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
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?
Dec 28 '11 #2

NeoPa
Expert Mod 15k+
P: 31,709
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT Year([DateTerminated]) - Year([DateHired]) -
  2.        IIf(Month([DateTerminated]) = Month([DateHired]),
  3.        IIf(Day([DateTerminated]) < Day([DateHired]), 1,
  4.        IIf(Month([DateTerminated]) < Month([DateHired]), 1, 0))) AS [Years]
  5.      , (12 + Month([DateTerminated]) - Month([DateHired]) -
  6.        IIf(Day([DateTerminated]) < Day([DateHired]), 1, 0)) Mod 12 AS [Months]
  7.      , Day(DateAdd('d', -Day([DateHired]), DateAdd('m', 1, [DateHired]))) AS [DIM]
  8.      , ([DIM] + Day([DateTerminated]) - Day([DateHired])) Mod [DIM] AS [Days]
Let us know how well that works for you.
Dec 28 '11 #3

Post your reply

Sign in to post your reply or Sign up for a free account.