2 Replies Latest reply on Apr 7, 2008 1:33 PM by cmadeira

TIMESTAMPDIFF and SQL_TSI_MONTH

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
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
Thanks Mike, this works :-)