month between 2 dates

[Bob Stearns]

I need to print the number of months between 2 dates. The best (easy)
solution I could come up with was the approximation int( (days(end_date)
- days(begin_date) / (365.24/12)) which is accurate enough for most
uses, but I wondered if there were a "monthsdiff( date1, date2)"
somewhere in the documentation I haven't found, probably by another name?

[Phil Sherman]

Have you considered:
months_between = year(end)*12+month(end) - (year(start)*12+month(start))

Bob Stearns wrote:

I need to print the number of months between 2 dates. The best (easy)
solution I could come up with was the approximation int( (days(end_date)
- days(begin_date) / (365.24/12)) which is accurate enough for most
uses, but I wondered if there were a "monthsdiff( date1, date2)"
somewhere in the documentation I haven't found, probably by another name?

[Bob Stearns]

Phil Sherman wrote:

Have you considered:
months_between = year(end)*12+month(end) - (year(start)*12+month(start))

Bob Stearns wrote:

I need to print the number of months between 2 dates. The best (easy)
solution I could come up with was the approximation int(
(days(end_date) - days(begin_date) / (365.24/12)) which is accurate
enough for most uses, but I wondered if there were a "monthsdiff(
date1, date2)" somewhere in the documentation I haven't found,
probably by another name?

I had not; probably a senior moment :-). Thank you.

[Brian Tkatch]

Do the days matter?

MONTH(End_Date) - MONTH(Begin_Date)

B.

[Phil Sherman]

This doesn't work when the dates span years.

Phil Sherman

Brian Tkatch wrote:

Do the days matter?

MONTH(End_Date) - MONTH(Begin_Date)

B.

[Serge Rielau]

Phil Sherman wrote:

This doesn't work when the dates span years.

Phil Sherman

Brian Tkatch wrote:

Do the days matter?

MONTH(End_Date) - MONTH(Begin_Date)

B.

(12 * YEAR(end_date) + MONTH(end_date))
- (12 * YEAR(begin_date) + MONTH(begin_date))

Something like that (untested....)

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab