It all looks correct to me ...

Your result of your first statement
year(dt-dt) is correct
- it is 1 year 11 months and 30 days - hence the value "1"

The rest of the statements fall into the same definition

To get the number of months between the date arithmetic, multiply the
Year result by 12 and add to number of months like :

(YEAR(DATE('2008-12-01')-DATE('2006-12-02'))*12)+(month(DATE('2008-12-01
')-DATE('2006-12-02')))

This results in 23 months = to be a true 2 years the second date need to
be 2006/12/01 not 02 ....

HTH

William B. Gannon
- IBM Certified Solutions Expert
- IBM Certified Database Administrator
DB2 Universal Database V8.1 for zOS

-----Original Message-----
From: Olsen Richard N.
[mailto:db2udbdba-ezmlmshield-x88957187.[Email address protected]
Sent: Monday, March 10, 2008 11:08 AM
To: LazyDBA Discussion
Subject: Month calculations Db2

Hi all,



I am writing a stored procedure that needs to be able to calculate the
number of months for a budget span. For the majority of consumers, the
budget span will equal one year. However, there are "special" cases that
a consumer's budget can extend beyond the one year (12 month) period.
The issue that I am having is that I am not finding a method that
accurately calculates the number of months. Below are some of the SQL
statements that I have tested and their result. I'm beginning to think
that may be I should use the number of days divided by 30 (or 31) to
calculate the number of months. If you have any advice or suggestions I
certainly would appreciate hearing what you have to say. I'm using Db2
7.1 on z/OS platform.





SELECT YEAR(DATE('2008-12-01')-DATE('2006-12-02'))

AS YEAR_BETWEEN

FROM SYSIBM.SYSDUMMY1

YEAR_BETWEEN: 1 the value should be 2.



SELECT YEAR('2008-12-01')-YEAR('2006-12-02')

AS YEAR_BETWEEN

FROM SYSIBM.SYSDUMMY1

YEAR_BETWEEN: 2 the value is correct.



*****************************************************



SELECT MONTH(DATE('2008-12-01')-DATE('2006-12-02'))

AS MNTH_BETWEEN

FROM SYSIBM.SYSDUMMY1

MNTH_BETWEEN: 11 the value should be 24.



SELECT MONTH('2008-12-01')-MONTH('2006-12-02')

AS MNTH_BETWEEN

FROM SYSIBM.SYSDUMMY1

MNTH_BETWEEN: 0 the value should be 24



******************************************************



SELECT DAYS('2008-12-01')-DAYS('2006-12-02')

AS DAYS_BETWEEN

FROM SYSIBM.SYSDUMMY1

DAYS_BETWEEN: 730 the value is correct.





Richard N. Olsen

Database Administrator

SC Department of Disabilities & Special Needs

Phone: (803) 898-9811

Email: [Email address protected]





---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



-----------------------------------------
Under Florida law, e-mail addresses are public records. If you do
not want your e-mail address released in response to a public
records request, do not send electronic mail to this entity.
Instead, contact this office by phone or in writing.

DB2 & UDB email list listserv db2-l LazyDBA home page