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]




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