MONTH is not a monotonic function - it only returns the month portion of the
date (in the range 1 through 12), not the number of months in a date.
To do that, you need something like: VALUES( MONTH(CURRENT DATE) +
((YEAR(CURRENT DATE) * 12)) - MONTH('YYYY-MM-DD') - ((YEAR('YYYY-MM-DD') *
12)))
Regards
Alex Levy
Sustainable Software Ltd.
-----Original Message-----
From: Olsen Richard N.
[mailto:db2udbdba-ezmlmshield-x88957187.[Email address protected]
Sent: 10 March 2008 15:08
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
DB2 & UDB email list listserv db2-l LazyDBA home page