How do I get # months between two DATEs?

[Jane1]

Hi,

I'm trying to query for the number of months between two dates. I have a table with a StartDate column and an EndDate column. I want to know the difference between them.

i.e.
SELECT StartDate - EndDate
FROM MyTable
WHERE ID = 1
(error: this query gives number of DAYS, not months)

MyTable's columns are defined as follows:
ID As Integer,
StartDate AS DATE FORMAT 'YY/MM/DD' NOT NULL,
End_Date DATE FORMAT 'YY/MM/DD' NOT NULL

Thanks!

 

[tdatgod]

The default interval is DAYS. you need to just tell it you want months.....

SELECT (StartDate - EndDate) month(4)
FROM MyTable
WHERE ID = 1

the (4) is the number of digits to use in the display

month(1) -- 0 - 9 months
month(2) -- 0 - 99 months
Month(3) -- 0 - 999 months
month(4) -- 0 - 9999 months

4 is the maximum percision allowed and allowed up to 833 years worth of months.

If you exceed the maximum value allowed you will get a

*** Failure 7453 Interval field overflow.

please see

Thread328-150396

for more information on the ANSI interval operators.

----