how to use year function in cobol-db2



Support for OS/VS COBOL, VS COBOL II, COBOL for OS/390 & VM and Enterprise COBOL for z/OS

how to use year function in cobol-db2

Postby waseem » Tue Nov 09, 2010 8:21 pm

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
waseem
 
Posts: 1
Joined: Tue Nov 09, 2010 8:05 pm
Has thanked: 0 time
Been thanked: 0 time

Re: how to use year function in cobol-db2

Postby dick scherrer » Wed Nov 10, 2010 2:43 am

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?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: how to use year function in cobol-db2

Postby GuyC » Wed Nov 10, 2010 2:05 pm

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.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: how to use year function in cobol-db2

Postby Quasar » Wed Nov 10, 2010 8:13 pm

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.
Quasar Chunawala,
Software Engineer, Lives at Borivali, Mumbai
User avatar
Quasar
 
Posts: 102
Joined: Wed Nov 10, 2010 7:11 pm
Location: Borivali, Mumbai
Has thanked: 13 times
Been thanked: 2 times

Re: how to use year function in cobol-db2

Postby GuyC » Wed Nov 10, 2010 8:25 pm

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
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times


Return to IBM Cobol

 


  • Related topics
    Replies
    Views
    Last post