Page 1 of 1

how to use year function in cobol-db2

PostPosted: Tue Nov 09, 2010 8:21 pm
by waseem
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

Re: how to use year function in cobol-db2

PostPosted: Wed Nov 10, 2010 2:43 am
by dick scherrer
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?

Re: how to use year function in cobol-db2

PostPosted: Wed Nov 10, 2010 2:05 pm
by GuyC
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.

Re: how to use year function in cobol-db2

PostPosted: Wed Nov 10, 2010 8:13 pm
by Quasar
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.

Image

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.

Re: how to use year function in cobol-db2

PostPosted: Wed Nov 10, 2010 8:25 pm
by GuyC
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