IBM Mainframe Forum

in my program i have to calculate the no. of years between two dates ie.,current date and a another date stored in a working storage variable


but wn iam using
exec sql
select year( current_date - :ws-date )
into :ws-year
from sysibm.sysdummy1
end-exec

*01 ws-date pic x(10) value '1990-10-13'
01 ws-year pic x(10).


its not working...so someone suggest me the correct data types and format for the date variables and syntax for the above query

Hello and welcome to the forum,

The duplicate post has been removed.

its not working...

If you post "it didn't work" it just wastes everyone's time.

What happened? Was there some unexpected result? Was there an abend? Was there an sqlcode that needs to be resolved?

it depends on the definition of difference in years:
1/jan/2011 - 31/dec/2010 = 1 year or 1 day ?
1/jan/2011 - 2/jan/2010 = ?
31/dec/2011 - 1 /jan/2010 = ?


why do you need DB2 to do this?
I'm pretty sure you can subtract 1990 from 2010 in cobol.

Waseem -

To get an Integer representation of the date in hand, one may apply the DAYS() Function. Here's how the DAYS() function works,

SELECT DAYS('0001-01-01')
FROM SYSIBM.SYSDUMMY1;
---------+---------+-----
1

SELECT DAYS('0002-01-01')
FROM SYSIBM.SYSDUMMY1;
---------+---------+-----
366

You can use the DAYS Function to find out the difference-in-days between two dates. If the Difference in Dates >= 365, it means they are one-year apart. This is a fool-proof test.



Hope this helps you in your quest to understand about DB2 Functions. I suggest that, you should refer to DB2 Manuals on Date Functions, and give it a read. Moreover, this was more relevant to the DB2 Forum.

Thank you and please get back to me, in case of queries.

Quasar wrote:If the Difference in Dates >= 365, it means they are one-year apart. This is a fool-proof test.

This completely discards leap years : 2008-02-28 - 2007-03-01 = 365, but not 1 year apart
difference is >= 3650 days doesn't mean they are 10 years apart

integer(current_date - datcol ) / 10000 give you the number of years , rounded down