# months between two dates

Posted in 2006

Summary

A user asked how to get the number of whole months between two DATE columns, since d2-d1 only yields days. Replies offered two working approaches: arithmetic on the date parts, (year(d2)*12+month(d2))-(year(d1)*12+month(d1)), and casting the dates to DATETIME YEAR TO MONTH and subtracting to get an INTERVAL MONTH(n) TO MONTH (shown both as a stored procedure and as an inline SELECT cast). The poster confirmed both worked for his date ranges. Others cautioned that "months between dates" is ambiguous, since month lengths vary and the casting method ignores day-of-month differences.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: General Discussion

PAUL MARVIN — Tue, 05 Dec 2006 15:44:24 — source: IIUG Forums & Mailing Lists

I have two dates D1 and D2. I want to know how many months between them. The expression d2-d1 returns the number of days but I can not find an expression to get number of months. If the answer is months and some days I want to truncate this to months only. Any ideas?

↪ replying to PAUL MARVIN

Mike Aubury — Tue, 05 Dec 2006 15:52:06 — source: IIUG Forums & Mailing Lists

Could do something like : (year(d2)*12+month(d2))- (year(d1)*12+month(d1)) On Tuesday 05 December 2006 15:44, PAUL MARVIN wrote: > I have two dates D1 and D2. I want to know how many months between them. > The expression d2-d1 returns the number of days but I can not find an > expression to get number of months. If the answer is months and some days I > want to truncate this to months only. Any ideas? > > > *************************************************************************** >**** Forum Note: Use "Reply" to post a response in the discussion forum. -- Mike Aubury

↪ replying to PAUL MARVIN

Mark Jamison — Tue, 05 Dec 2006 15:57:26 — source: IIUG Forums & Mailing Lists

select month(D1) - month(D2) a, from tab1 ----- Original Message ---- From: PAUL MARVIN <marvinp@chubb.com> To: ids@iiug.org Sent: Tuesday, December 5, 2006 9:44:24 AM Subject: # months between two dates [7918] I have two dates D1 and D2. I want to know how many months between them. The expression d2-d1 returns the number of days but I can not find an expression to get number of months. If the answer is months and some days I want to truncate this to months only. Any ideas? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to PAUL MARVIN

vze2qjg5@verizon.net — Tue, 05 Dec 2006 16:48:27 — source: IIUG Forums & Mailing Lists

There is no such thing as a 'month' in an interval (the period between two dates). Think about it - is it a 28 day month? or a 31 day month? You can take the number of days and do whatever you like to it to approximate a month. j. >From: PAUL MARVIN <marvinp@chubb.com> >Date: 2006/12/05 Tue AM 09:44:24 CST >To: ids@iiug.org >Subject: # months between two dates [7918] > >I have two dates D1 and D2. I want to know how many months between them. The >expression d2-d1 returns the number of days but I can not find an expression >to get number of months. If the answer is months and some days I want to >truncate this to months only. Any ideas? > > >******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to PAUL MARVIN

ART KAGEL, BLOOMBERG/ 731 LEXIN — Tue, 05 Dec 2006 16:51:06 — source: IIUG Forums & Mailing Lists

create procedure monthdiff( d1 date, d2 date ) returns interval month(3) to month; define dt1, dt2 datetime year to month; define res interval month(3) to month; let dt1 = d1; let dt2 = d2; let res = dt2 - dt1; return res; end procedure; Art S. Kagel ----- Original Message ----- From: Paul Marvin <ids@iiug.org> At: 12/05 10:53:53 I have two dates D1 and D2. I want to know how many months between them. The expression d2-d1 returns the number of days but I can not find an expression to get number of months. If the answer is months and some days I want to truncate this to months only. Any ideas? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Mike Aubury

PAUL MARVIN — Tue, 05 Dec 2006 17:40:38 — source: IIUG Forums & Mailing Lists

Thanks Mike! For your needs this works just fine.

↪ replying to ART KAGEL, BLOOMBERG/ 731 LEXIN

PAUL MARVIN — Tue, 05 Dec 2006 17:49:23 — source: IIUG Forums & Mailing Lists

Thanks Art! This works as well as Mike Aubury's solution which is good enough for the types of date ranges we will be using here.

↪ replying to PAUL MARVIN

Stuart McCann — Thu, 07 Dec 2006 00:18:37 — source: IIUG Forums & Mailing Lists

And one more based on Arts. SELECT ((dtcol1::DATETIME YEAR TO MONTH)-(dtcol1::DATETIME YEAR TO MONTH))::INTERVAL MONTH(9) TO MONTH FROM aTable ; Stuart McCann Phone: 02 6332-8285 stuart.mccann@lands.nsw.gov.au -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of PAUL MARVIN Sent: Wednesday, 6 December 2006 4:49 AM To: ids@iiug.org Subject: Re: # months between two dates [7924] Thanks Art! This works as well as Mike Aubury's solution which is good enough for the types of date ranges we will be using here. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. *************************************************************** This message is intended for the addressee named and may contain confidential information. If you are not the intended recipient, please delete it and notify the sender. Views expressed in this message are those of the individual sender, and are not necessarily the views of the Department of Lands. This email message has been swept by MIMEsweeper for the presence of computer viruses. ***************************************************************

↪ replying to ART KAGEL, BLOOMBERG/ 731 LEXIN

Jonathan Leffler — Sat, 09 Dec 2006 23:02:53 — source: IIUG Forums & Mailing Lists

On 12/5/06, ART KAGEL, BLOOMBERG/ 731 LEXIN <kagel@bloomberg.net> wrote: > > create procedure monthdiff( d1 date, d2 date )> returns interval month(3) to month; > > define dt1, dt2 datetime year to month; > define res interval month(3) to month; > > let dt1 = d1; > let dt2 = d2; > let res = dt2 - dt1; > > return res; > end procedure; If this has the required semantics, that's good. I wonder if the answer 1 is expected for adjacent dates such as 2006-01-31 and 2006-02-01 while the answer 0 is expected for non-adjacent dates such as 2006-01-01 and 2006-01-31? I tend to think that the one day interval is less significant than the 30 day interval. Someone else already pointed out that the correctness of an answer depends on your interpretation of months between two dates. I agree. > ----- Original Message ----- > From: Paul Marvin <ids@iiug.org> > At: 12/05 10:53:53 > > I have two dates D1 and D2. I want to know how many months between them. The > expression d2-d1 returns the number of days but I can not find an expression > to get number of months. If the answer is months and some days I want to > truncate this to months only. Any ideas? -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/