2 Replies Latest reply: Apr 7, 2008 8:33 AM by cmadeira RSS

    TIMESTAMPDIFF and SQL_TSI_MONTH

    cmadeira
      Hello All,

      I'm trying to return the difference in months between two dates using the formula:

      TIMESTAMPDIFF( SQL_TSI_MONTH , CURRENT_DATE , Expected Close Date)

      The Results i'm getting are:

      Expected Close Date,     CURRENT_DATE,     SQL_TSI_DAY, SQL_TSI_MONTH

      30/03/2008,     07/04/2008,     -8,     0
      30/04/2008,     07/04/2008,     23,     1
      30/05/2008,     07/04/2008,     53,     2

      If I use SQL_TSI_DAY this returns the figures I would expect. Using SQL_TSI_MONTH I would expect -1, 0 and 1. Any Ideas??

      Thanks,

      Oliver
        • 1. Re: TIMESTAMPDIFF and SQL_TSI_MONTH
          599892
          It is rounding the result.

          For example, The difference between May 30 and April 7 is 2 months because it rounds the number of whole months up to 2. April 7 - May 7 = 1 Month and May 7 - May 30 = arounf 75% of a month... diff is 1.75 which rouonds to 2.

          Difference betwee May 30 and April 17 however, is 1 month. April 17 - May 17 = 1 and May 17 - May 30 = 40ish%... 1.4 rounds to 1.

          You might want to consider doing a simple subtraction equation like
          MONTH(Date 1) - MONTH(Date 2) + ((YEAR(Date 1) - YEAR(Date 2)) * 12)

          Mike L
          • 2. Re: TIMESTAMPDIFF and SQL_TSI_MONTH
            cmadeira
            Thanks Mike, this works :-)