how to calculate the month difference

[Guest]

how to calculate the month difference between two dates using Access SQL?

 

[Guest]

If I understand your question correctly (?), use the DateDiff function. Here
is an example, which you can copy and paste into the Immediate window (open
with Ctrl G):

?Datediff("m",#15-Aug-2005#,Now())

It would be helpful to not be so frugal with your written words, so that we
can understand exactly what you need.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Thanks for your response!

I tried DateDiff() before. The result is not what I want.
For example, I want to calculate the months between 2007-03-01 and
2007-02-28, I expect the result is 0, but the result will be 1 if I use
DateDiff().

Thanks.

 

[Guest]

Perhaps you can use the "ww" argument of the DateDiff function, and define
your own month interval, by dividing the result by 4. Something like this:

?Datediff("ww",#2007-02-28#,#2007-03-01#)/4
0

Here are some other test results:

?Datediff("ww",#2007-02-14#,#2007-03-01#)/4
0.5

?Datediff("ww",#2007-02-7#,#2007-03-01#)/4
0.75

?Datediff("ww",#15-Aug-2005#,Now())/4
20

Notice how this last example gave me 20 months difference, versus using the
"m" argument, which yields 19 months difference:

?Datediff("m",#15-Aug-2005#,Now())
19

Also, here is a link to a page that discusses a topic that might be of
interest to you:

International Dates in Access
http://allenbrowne.com/ser-36.html


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Thanks Tom.
But actually, the months between today and 15-Aug-2005 is 18. So I think
this approach doesn't work.

 

[Guest]

Would you be happy with using 4.3333 (ie. 52 Weeks per year/12 months per
year = approx. 4.3333 weeks per month) in place of 4?

?Datediff("ww",#15-Aug-2005#,Now())/4.3333
18.4616804744652

You can apply a format to reduce the number of decimal places:

?Format(Datediff("ww",#15-Aug-2005#,Now())/4.3333,"0.0")
18.5

or even this:

?Format(Datediff("ww",#15-Aug-2005#,Now())/4.3333,"0")
18


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Thanks again.

I tried your approach using following testing case:

Format(Datediff("ww",#28-FEB-2007#,#18-MAR-2007#)/4.3333,"0")

The reult is 1, which is NOT my expected result: 0.
I don't know why.

 

[Douglas J. Steele]

DateDiff counts the number of changes. When you're looking at DateDiff("m",
#28-Feb-2007#, #01-Mar-2007#), because you're looking at months, it counts
how many times the month changes between the two dates, and (correctly)
returns 1.

DateDiff("ww", #28-Feb-2007#, #18-Mar-2007#) returns 3, because the week
changes 3 times between those two dates. 3/4.3333 returns
0.692313017792444, but when you apply a format of "0", that gets rounded to
1.

It sounds as though you want the number of full months between the two
dates. In other words, you want DateDiff("m", #28-Feb-2007#, #27-Mar-2007#)
to return 0, but DateDiff("m", #28-Feb-2007#, #28-Mar-2007#) or
DateDiff("m", #28-Feb-2007#, #29-Mar-2007#) to return 1.

To accomplish that, check whether the day of the second date is less than
the day of the first date, and subtract 1 from what DateDiff returns if it
is:

DateDiff("m", Date1, Date2) - IIf(Day(Date1) > Day(Date2), 1, 0)

 

[Guest]

Thanks Douglas.
I tried your approach already.
I think it need to do some enhancement to cater some odd cases, for example:
the months between 31-MAY-2007 and 30-JUN-2007 should be 1, but result will be
0 if I use your suggested method.
Could you please help me to cater this case?

 

[Jamie Collins]

I think it need to do some enhancement to cater some odd cases, for example:
the months between 31-MAY-2007 and 30-JUN-2007 should be 1

I don't think you've really explained how your expected value is
calculated so here's my guess at the logic (the following SQL is for
demonstration purposes only: I wouldn't normally split up SQL like
this, rather I'd do it in one, but this shows the logic better):

SELECT #2007-05-31# AS date1,
#2007-06-30# AS date2,
DATEDIFF('M', date1, date2) AS diff_months,
DATEADD('M', diff_months, date1) AS date1_plus_diff_months,
diff_months - IIF(date2 < date1_plus_diff_months, 1, 0) AS result

Jamie

--