Re: Interval between Dates ?
Christian Jachmann (jachmann@dsl.mayn.de) wrote:
: following statement works fine:
: select enddate - begindate
: from customer
: where nr=1
: (enddate and begindate are Date-types)
: Reports the number of days between 'enddate' and 'begindate' -> OK.
: But, what I need is:
: the exact number of MONTHS between 'enddate' and 'begindate'
Here's the test data and the SELECT statement that gets what you want:
CREATE TABLE try
(s_date DATE,
e_date DATE);
INSERT INTO try VALUES ("01/01/1995", "12/15/1995");
INSERT INTO try VALUES ("01/01/1995", "01/15/1995");
INSERT INTO try VALUES ("01/01/1995", "01/15/1996");
INSERT INTO try VALUES ("01/01/1995", "02/15/1996");
INSERT INTO try VALUES ("01/01/1995", "02/15/1997");
INSERT INTO try VALUES ("01/01/1995", "02/15/1998");
INSERT INTO try VALUES ("01/01/1995", "02/15/1999");
INSERT INTO try VALUES ("01/01/1995", "02/15/2000");
INSERT INTO try VALUES ("01/01/1995", "02/15/2001");
INSERT INTO try VALUES ("03/01/1995", "02/15/1996");
INSERT INTO try VALUES ("08/01/1995", "02/15/1996");
SELECT s_date, e_date,
(12 * (YEAR(e_date) - YEAR(s_date)) +
(MONTH(e_date) - MONTH(s_date)))
FROM try
ORDER BY e_date, s_date;
If you want to include the start date or end date in the calculation,
add 1 to the math
=======================================================================
Dennis J. Pimple dennisp@informix.com Opinions expressed
Senior Consultant -------------------- are mine, and do not
Informix Software Inc Voice: 303-850-0210 necessarily reflect
Denver Colorado USA Fax: 303-779-4025 those of my employer.
: ------------------------------------------------------------------------
: | Christian Jachmann | Linux ! |
: | 09732/3721 (privat) | the choice of a GNU | root@habbib.mayn.sub.de
: | 09732/904115 (work) | Generation | root@dsl.mayn.sub.de
--