• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

Calc the difference in months.

I have the following fields.



I want to find the number of months between the 2
Michael Franz
Michael Franz
1 Solution
Daniel WilsonCommented:
have a look at http://technet.microsoft.com/en-us/library/ms189794.aspx

select datediff(month, asicPolicyInfo_assoc_PolEffDat, basicPolicyInfo_assoc_PolExpDate) from myTable;
Michael FranzCFOAuthor Commented:
sorry, I mispoke the program uses Java. Not sure what to do now.... I did find this....but could not get it to work.

monthDiff = endDate.get(Calendar.MONTH) - startDate.get(Calendar.MONTH);
Daniel WilsonCommented:
OK, so it's not a SQL question, but a Java question, and they're not "fields" but variables.  Right?

<Link to StackOverflow removed>
I'd have a look at the joda-time library.

Sorry, I'm no Java expert.
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Alexandre SimõesManager / Technology SpecialistCommented:
Date handling it's simply a mess in JAVA... a huge mess...
You'll fund yourself casting and converting to cast and convert again to get something done.

My advise is to use Joda-Time to deal with dates.

Use the monthsBetween to get it done.
Amitkumar PSr. ConsultantCommented:
Using Joda Time is certainly a solid choice.

If you don't wish to do that you can compute the difference like this, just using the standard JDK - assuming cal2 is after cal1:

int monthDiffs(Calendar cal1, Calendar cal2) {
               if (cal1.after(cal2))
                   throw new IllegalArgumentException("Cal2 should be after cal1") ;

            // Add in any year differences
            int months = (cal2.get(Calendar.YEAR) - cal1.get(Calendar.YEAR))*12 ;
            // Then add the months different within this year
            months += cal2.get(Calendar.MONTH) - cal1.get(Calendar.MONTH) ;
            // Allow for which day we're on
            if (cal2.get(Calendar.DAY_OF_MONTH) < cal1.get(Calendar.DAY_OF_MONTH))
                  months -= 1 ;

               return months ;
zzynxSoftware engineerCommented:
Antoher way of doing it in plain java without Joda Time is simply in a loop where you keep adding a month until your date falls after the 2nd date:

int monthDiffs(date date1, Date date2) {
    if (date1.after(date2))
                   throw new IllegalArgumentException("Date2 should be after date1") ;    

    Calendar cal = Calendar.getInstance();
    int nrOfMonths = 0;

    do {
         cal.add(Calendar.MONTH, 1);
    } while (cal.getTime().before(date2));

    return nrOfMonths; // or nrOfMonths-1; according to your taste (*)

Open in new window

(*) = If the dates are eg. 15-dec-2012 and 1-Jan-2013, do you consider that as a month difference of 1 or 0?

will give you days. You can then compute months (which is somewhat subjective anyway)
Michael FranzCFOAuthor Commented:
thanks for all the help. I finally get to move on to completing the report... Yea!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now